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 fo the method.

SP_SPACEUSED TableName

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

Posted by Mitchel on Friday, July 18, 2008
Tags: Tutorials, SQL
 

Comments

EXEC GetAllTableSizes

CREATE PROCEDURE GetAllTableSizes
AS
/*
Obtains spaced used data for ALL user tables in the database
*/
DECLARE @TableName VARCHAR(100) --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName

--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT *
FROM #TempTable

--Final cleanup!
DROP TABLE #TempTable

GO

By jack.mike.info on Thursday, April 14, 2011 at 3:26 AM

Name (required)

Email (required)

Website

CAPTCHA image
Enter the code shown above:

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.

Friend of RedGate

www.datasprings.com - DotNetNuke Modules ICG Hosting

Click here for advertising information.

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.