Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Automated Monitoring Database Size Using sp_spaceused

By David Poole,

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

name rows reserved data index_size unused
Employee 290 176 KB 56 KB 120 KB 0 KB

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'

name rows reserved data index_size unused
Employee 290 176 KB 56 KB 120 KB 0 KB

name rows reserved data index_size unused
Employee 18 24 KB 16 KB 8 KB 0 KB

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

name rows reserved data index_size unused
Employee 290 176 KB 56 KB 120 KB 0 KB

name rows reservedKB dataKB index_sizeKB unusedKB
HumanResources.Employee 290 176 56 120 0

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.

DatabaseName DateCreated TableName NumberOfRows Reserved Data IndexSize Unused
AdventureWorks 2006-12-01 22:26:52.467 dbo.AWBuildVersion 1 16 8 8 0
AdventureWorks 2006-12-01 22:26:52.467 dbo.DatabaseLog 451 1696 1616 24 56
AdventureWorks 2006-12-01 22:26:52.467 dbo.ErrorLog 0 0 0 0 0
AdventureWorks 2006-12-01 22:26:52.467 HumanResources.Department 16 32 8 24 0
AdventureWorks 2006-12-01 22:26:52.467 HumanResources.Employee 290 176 56 120 0
AdventureWorks 2006-12-01 22:26:52.467 HumanResources.EmployeeAddress 290 48 16 32 0
...etc

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.

Total article views: 19062 | Views in the last 30 days: 25
 
Related Articles
BLOG

sp_spaceused system-stored procedure

You can use the sp_spaceused system-stored procedure to return space usage information about a datab...

FORUM

sp_spaceused

sp_spaceused

FORUM

sp_spaceused tablename for each database

I've been asked to put something together that will go through EVERY database on a server and put th...

FORUM

spaceused

Hi guys!   My database is getting kind of big, so I have run sp_spaceused in my database to check ...

FORUM

sp_spaceused returns same even after deleteing 3.5 lac records.

sp_spaceused returns same even after deleteing 3.5 lac records.

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones