FILEGROUP FULL ...

[JayKusch]

I am aware that a Full Filegroup will prohibit INSERTS into the DB BUT ... Will this also prevent UPDATE and DELETE operations.

Thanks

J. Kusch

 

[nigelrivett]

Depends - and it might not stop inserts.
If the operation causes a new extent to be allocated then it will fail.

This can happen on an insert of course if it allocates a new page - the update things apply too.

An update can cause a page split in the data page if the size of a variable length field increases or if a clustered index field is changes. It can cause a index page split if an index field is updated.

For a delete I guess it could cause statistics to be re-evaluated - but I don't think that would cause the delete to fail. Maybe it could cause an index update. I have a feeling I'm forgetting something here - I have a feeling a delete can fail but can't think why.

Anyway you should stop everything before you get a corrupt database.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

Creating a 1MB database and repeated inerts until it fills allows updates and deletes.

An update can certainly fail - not sure about deletes.




======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[JayKusch]

Here is the info I received from MS ...


won't prevent DELETE. It might prevent UPDATE if:

The update need to allocate more space on some page (if you expend a column so it won't fit on the old page so the update need to be performed internally as a delete followed by an insert). There is no page with free space to hold the new to of the pages that the table is currently using.
There is no extent with free pages of the extents that the tables is using. There are no un-allocated extents in the filegroup.


Thanks

J. Kusch

 

[nigelrivett]

They've missed the bit about page splitting in indexes.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.