January 27, 2005 at 10:54 pm
I think this question would be for the Gurus of SQL Server. I am trying to figure out the o/p of the stored procedure sp_SpaceUsed. This question is not about updating statistics or anything. I am trying to figure out the output. All the output columns are self explanatory except the "unused" column. Even when the table has no text columns in it for some of the tables I see some unused space. What does it mean?
If I think that upon row insertion a page is added to table if required, and then this row is deleted, this page for some reason stays allocated to the table. Is that how this would happen?
On the other hand I see some tables that show a "negative" number in the unused column. What would that be? I was thinking that this could be that there are new row inserts and new pages have to be allocated to the table and the indexes so it is showing that these many number of KB of data needs to be added to the table. So issued a "CheckPoint" and checked the space used again. I got the same negative results.
I checked and rechecked the output, before and after updating statistics and checkpoint. No difference in the result. Does anyone know what this "unused" space is?
Any help would be appreciated. Thanks in advance.
January 28, 2005 at 8:16 am
For unused space, SQL Server does not auto-shrink and shuffle data onto other pages when you delete rows, so whatever space was allocated stays alocated when you delete. However, before going crazy with shrinking, read articles like this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Read BOL under the DBCC UPDATEUSAGE topic for an explanation of negative unused space.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply