In one of my previous blog entries, I provided a helpful script that would allow you to find the space used for all SQL Server tables in a single database. It was a very simple method using the SP_SPACEDUSED stored procedure that is included in SQL Server. In that article, I touched a bit on the general usage for the method.
SP_SPACEUSED TableNameWhere TableName was the specific table you are researching. Well, there is a secondary option with a second parameter that is a very helpful item as well. I'll provide a quick scenario that sets up my specific example. I was working with a table that has about 80 columns, and somewhere in the neighborhood of 1.8 million records. Trying to condense the size of the database, we removed 20 columns that were no longer necessary. Prior to dropping the table, we ran SP_SPACEUSED to get the table size, which provided us the following.
Reserved: 5,474,304 KB Data: 3,451,368 KB Index_Size: 196,704 KB
Overall the table itself was in the neighborhood of 3Gb, we executed a statement that dropped 20 columns from the table, however, running SP_SPACEUSED for the table only provided us the same results. You can pass SP_SPACEUSED a second parameter which expects a value of 'true' or 'false' with a default of false. If it is set to true, it will update usage information before returning the results. We then ran the following statement
SP_SPACEUSED TableName 'true'Now we got the accurate information that showed our reduction in size.
Reserved: 1,826,320 KB Data: 1,727,624 KB Index_Size: 98,344 KB
I hope that this might help those of you optimizing table sizes and noticing incorrect numbers!