July 18, 2008

SQL Server Table Size Calculation

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.


Where 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!

tags: Tutorials, SQL
comments powered by Disqus

Content provided in this blog is provided "AS-IS" and the information should be used at your own discretion.  The thoughts and opinions expressed are the personal thoughts of Mitchel Sellers and do not reflect the opinions of his employer.

Content Copyright

Content in this blog is copyright protected.  Re-publishing on other websites is allowed as long as proper credit and backlink to the article is provided.  Any other re-publishing or distribution of this content is prohibited without written permission from Mitchel Sellers.