Ellen-477471 (4/10/2013)
I am wondering if it is the way SQL Server 2005 allocates a new extent to a table that needs to grow.Would it be a percentage of the existing size of the table?
Nope. An extent at a time. So if table needs 1 kb more space, it gets an extent (64 kb), providing it's large enough to use uniform extents (a few kb). If the table needs 100kb more space, it gets 2 extents, etc.
As for a large increase in space...
- rebuild index with lower fill factor
- lots of inserts into the 'middle' of the clustered index
- lots of updates that grow the row (eg updating a null value to non-null)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability