We’ve all experienced a database administrator coming up to us and questioning why a SQL Server database is consuming so much disk space.  If you’re like me, you would immediately run the sp_helpdb stored procedure to see if the log file has grown out of control:

EXEC sp_helpdb 'AdventureWorks'

The output of the sp_helpdb shown below is rather helpful:

DatabaseSizeStoredProcedureResults

Here we can see the total size of the database.  We can also see the portion of that total that is data and the portion that is logs.  This data is great, but what if you need more detailed information?  Well the sp_spaceused stored procedure will output the number of rows in the table, the reserved space, the space the data takes up, the space any indexes take up, and the unused space for whatever table you specify.  If we use this stored procedure along with a cursor, we can get detailed information about disk space usage for every table in our database.  See the example code below for a complete example of how to do this:

CREATE TABLE #temp
(
name varchar(100),
rows int,
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused varchar(100)
)

DECLARE tableCursor CURSOR FOR
SELECT S.name + '.' + T.name FROM sys.tables T
INNER JOIN sys.schemas S on S.schema_id = T.schema_id

DECLARE @tableName varchar(100)
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp
EXEC sp_spaceused @tableName

FETCH NEXT FROM tableCursor INTO @tableName
END

CLOSE tableCursor
DEALLOCATE tableCursor

SELECT * FROM #temp ORDER BY name

DROP TABLE #temp

This stored procedure should output something like the following:

TableSizeStoredProcedureResults

This stored procedure is rather simple so let’s go through what’s happening.  First, we create the temporary table named #temp to store the end results of our query.  Next we create a cursor that will loop through all of the tables in our database.  Now, we use standard cursor code to loop through all of the items in the cursor.  For each item we have, we execute the sp_spaceused stored procedure with a parameter of “SchemaName.TableName” and store the results in our temporary table.  Finally we close and deallocate the cursor we used, select all of the results out of the temporary table, and drop the temporary table.

So that about does it.  Hopefully next time your database administrator comes to you asking why your database is eating huge amounts of disk, you’ll have the tools in your arsenal to quickly give them an answer.

Determining Database Size in Microsoft SQL Server
Tagged on:

Leave a Reply