sp_spaceused is a useful stored procedure for telling you how much physical space your database or database object consumes. For example in the SQL 2005 AdventureWorks database type the following

exec sp_spaceused 'HumanResources.Employee'

This returns a recordset similar to the one shown below

These figures are derived from querying system tables however, if heavy delete activity occurs on the database then the procedure should be used with the @updateusage parameter as follows

exec sp_spaceused 'HumanResources.Employee','true'

This ensures that the usage statistics of the object are as up-to-date as possible

Taking this as a basis I decided that I wanted to use the the stored procedure as the basis for an automated database size monitoring system.

Starting point

There are two obvious points with sp_spaceused.

1. It does not list the SQL2005 schema to which the object belongs
2. The data size values are text values to include the KB comment

This means that if a database had two identically named tables in separate schemas there would be no way in which to tell the two apart. To illustrate this run the following statement in the Adventure works database to create a Sales.Employee table.

SELECT E.*
INTO Sales.Employee
FROM HumanResources.Employee AS E
INNER JOIN HumanResources.EmployeeDepartmentHistory AS EH
ON E.EmployeeId = EH.EmployeeId
WHERE EH.DepartmentId=3

Now run the following statements

exec sp_spaceused 'HumanResources.Employee'
exec sp_spaceused 'Sales.Employee'

Whether it is good design to have two objects with the same name in different schemas is a debate for another day. My point is that it is not possible to say to which table the results refer unless you know which command was executed to retrieve the data. This problem was going to have to be resolved if the automated solution was to work.

The 2nd issue was that the data included the KB suffix for data and index values and therefore this would have to be stripped if the results were to be exported for graphing purposes.

Building out own sp_spaceused

Back in February 2006 I wrote and article for this site on writing your own Utility Procedures. The article was for SQL2000 but we are going to use some of the techniques to build our solution.

First we are going to look at the source listing for the built in sp_spaceused. In the MASTER database run the following command.

exec sp_helptext 'sp_spaceused'

This returns the following

create procedure sys.sp_spaceused --- 2003/05/19 14:00
@objname nvarchar(776) = null,-- The object we want size on.
@updateusage varchar(5) = false-- Param. for specifying that
-- usage info. should be updated.
as

.....etc

In SQL2005 you cannot do anything to system catalogs or objects. In SQL2000 you could create objects that behaved as system objects and even update system tables but in SQL2005 system objects are sacrosanct. In some cases, as we shall see later, too sacrosanct.

In both versions of SQL Server you can run the following...

-- Allow updates on system tables.
exec sp_configure 'Allow Updates',1
RECONFIGURE WITH OVERRIDE
GO

But only in SQL2000 does it have any affect.

To create our own version of sp_space used we are going to change the create statement to the following:

CREATE PROCEDURE dbo.sp_DBA_spaceused

We also need to change the output portion of the stored procedure so we need to change the lines at the end of the procedure as follows:

SELECT   
  name = OBJECT_NAME (@id),  
  rows = convert (char(11), @rowCount),  
  reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),  
  data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),  
  index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages 
THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),  
  unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages 
THEN(@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')  

Should be altered to..

SELECT   
name = @objname,
rows = @rowCount,
reservedKB = @reservedpages*8 ,
dataKB = @pages * 8,
index_sizeKB = (CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8,
unusedKB = (CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8

We have achieved two things here

• We have told the procedure to return the object name that we pass into the procedure
• We are returning the space information as numeric values.

Testing out the new sp_DBA_spaceused

Run our altered script in the MASTER database and then run the following command

USE AdventureWorks
GO
exec sp_spaceused 'HumanResources.Employee'
exec sp_DBA_spaceused 'HumanResources.Employee'

Our output will appear as follows

sp_spaceused for all tables - 1st version

Ideally what we need is a stored procedure that runs sp_spaceused for all tables in a given database so we are going to create another procedure in the MASTER database.

USE master
GO
CREATE PROC dbo.sp_DBA_spaceused_AllTables
AS
SET NOCOUNT ON
-- Temporary table to catch the output from sp_DBA_spaceused
CREATE TABLE #T (
TableName SYSNAME NOT NULL,
NumberOfRows BIGINT NOT NULL ,
Reserved BIGINT NOT NULL ,
Data BIGINT NOT NULL ,
IndexSize BIGINT NOT NULL ,
Unused BIGINT NOT NULL 
)

-- We are only interested in user databases
IF DB_NAME() NOT IN ('master','model','msdb','tempdb','distribution')
BEGIN
DECLARE @UserTables TABLE(TableName NVARCHAR(256) NOT NULL)
INSERT INTO @UserTables
SELECT USR.name + '.' + OBJ.name
FROM dbo.sysobjects AS OBJ
INNER JOIN
sys.schemas AS USR
ON OBJ.uid = USR.schema_id
WHERE OBJ.type='U'
-- loop through all tables without using a cursor.
DECLARE @TableName NVARCHAR(256)
SET @TableName=''
WHILE @TableName IS NOT NULL
BEGIN
SELECT @TableName=MIN(TableName)
FROM @UserTables
WHERE TableName>@TableName
IF @TableName IS NOT NULL
BEGIN
INSERT INTO #T
exec sp_dba_spaceused @TableName
END
END
END
-- return our records
SELECT
DB_NAME() AS DatabaseName,
GETDATE() AS DateCreated,
TableName,
NumberOfRows ,
Reserved,
Data,
IndexSize,
Unused
FROM #T
-- clean up after ourselves.
DROP TABLE #t
GO

Running our new procedure in Adventureworks produces the following results.

sp_spaceused for all tables - 2nd version

Books on-line tells us that the same information can be extracted from two new system management views

• sys.dm_db_partition_stats
• sys.dm_db_index_physical_stats

By looking at the source code for sys.sp_spaceused in more detail I was able to see that it used these system views itself.

After much experimentation I was eventually able to come up with the procedure shown below.

CREATE PROC dbo.sp_DBA_spaceused_AllTables2
AS
SET NOCOUNT ON
SELECT
DB_NAME() AS DatabaseNase ,
GETDATE() AS DateCreated ,
TableName,
Rows ,
Reserved ,
Data ,
Used - Data AS IndexSize ,
Reserved - Used AS Unused
FROM (
SELECT 
USR.name + '.' + OBJ.name AS TableName ,
MIN(row_count) AS Rows ,
SUM(8 * reserved_page_count) + MAX(COALESCE(LOBDATA.LobReserved,0)) AS reserved,
SUM (8*
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
) AS Data,
SUM (8*used_page_count)+MAX(COALESCE(LOBDATA.LobUsed,0)) AS Used
FROM dbo.sysobjects AS OBJ
INNER JOIN sys.schemas AS USR
ON OBJ.uid = USR.schema_id
INNER JOIN sys.dm_db_partition_stats AS PS
ON PS.object_id = OBJ.id
LEFT JOIN(
SELECT 
parent_id,
SUM(8*reserved_page_count) AS LOBReserved,
SUM(8*used_page_count) AS LOBUsed
FROM sys.dm_db_partition_stats p 
INNER JOIN sys.internal_tables it
ON p.object_id = it.object_id
WHERE it.internal_type IN (202,204)
GROUP BY IT.parent_id
) AS LOBDATA
ON LOBDATA.parent_id = OBJ.Id

WHERE OBJ.type='U'
GROUP BY USR.name + '.' + OBJ.name
) AS DT

The advantage that this 2nd version has over the 1st is that the first runs multiple queries against the database where as the 2nd version is just a single query and therefore should incur less of a hit.

Validating the stored procedures

I performed several checks on these procedures to ensure that they were given consistent results

• Comparison between sp_SpaceUsed results and sp_DBA_Spaceused for each table
• Comparison between sp_DBA_spaceused_AllTables and the individual sp_spaceused results
• Comparison between sp_DBA_spaceused_AllTables and sp_DBA_spaceused_AllTables2

These checks may seem a bit repetitive however it was a good thing that I did check them because I found that my first incarnation of sp_DBA_spaceused_AllTables2 worked only when there was no XML field in the database. If I hadn't carried out these checks on Adventureworks it would be very easy to miss this glitch.

Running the procedures with sp_msforeachdb

The next step is to try and run the procedure with sp_msforeachdb.

exec master.dbo.sp_msforeachdb @command1="exec [?].dbo.sp_DBA_spaceused_AllTables"

... or ...

exec master.dbo.sp_msforeachdb @command1="exec [?].dbo.sp_DBA_spaceused_AllTables2"

sp_msforeachdb cycles through each database and substitutes the name of the database into the position marked by the question mark.

In effect it runs...

exec AdventureWorks.dbo.sp_DBA_spaceused_AllTables
exec AdventureWorksDW.dbo.sp_DBA_spaceused_AllTables
exec EntLibQuickStarts.dbo.sp_DBA_spaceused_AllTables

The final stage is to use the procedures to write their output into a database table so that you have an ongoing snapshot of what data growth within your databases.

Recording the results

The final step is to set up a database and table that will be used to store the output from your procedures. In the example below I use a global temporary table but in reality I would have a specific database called ServerMetrics holding permanent database table to record my output.

CREATE TABLE ##SpaceUsedResults (
DatabaseName SYSNAME NOT NULL,
DateCreated DATETIME NOT NULL,
TableName NVARCHAR(256) NOT NULL,
Rows BIGINT NOT NULL,
Reserved BIGINT NOT NULL,
Data BIGINT NOT NULL,
IndexSize BIGINT NOT NULL,
Unused BIGINT NOT NULL,
CONSTRAINT PK_SpaceUsed PRIMARY KEY CLUSTERED (DatabaseName,TableName,DateCreated)

)

First I tried running my original sp_DBA_spaceused_AllTables.

exec master.dbo.sp_msforeachdb @command1="INSERT INTO ##SpaceUsedResults exec 

This resulted in repeated errors as show below

Msg 8164, Level 16, State 1, Procedure sp_DBA_spaceused_AllTables, Line 35
An INSERT EXEC statement cannot be nested.

This is because sp_DBA_spaceused_AllTables uses

INSERT INTO #T
exec sp_DBA_spaceused @TableName

As a result you cannot use the output from sp_DBA_spaceused_AllTables to populate a table. There are ways around this but the best solution is to use sp_DBA_spaceused_AllTables2

exec master.dbo.sp_msforeachdb @command1="INSERT INTO ##SpaceUsedResults exec [?].dbo.sp_DBA_spaceused_AllTables2"
SELECT * FROM ##SpaceUsedResults

Conclusion

The overall idea behind sp_DBA_spaceused_AllTables2 is to run as part of a regular scheduled job in order to allow you to monitor the growth of your database over time.

The procedure has a very light footprint so even on servers with many many databases containing a large number of tables it is very quick to run.