Extend the sp_spaceUsed Procedure’s Usefulness in Obtaining Transaction Log Statistics

SQL Server supplies the sp_SpaceUsed stored procedure for getting information about database size. With this procedure, you can obtain the current database’s name, size, and amount of unallocated space, reserved space, space used by data, space used by indexes, and unused space. However, sp_SpaceUsed doesn’t provide information about the transaction log’s size or the amount of used space in it. Thus, I wrote the sp_LogSpace stored procedure, which Listing 1 shows. By compiling sp_LogSpace in a master database and calling it from an updated version of sp_SpaceUsed, you can extend the ability of sp_SpaceUsed to obtain transaction log statistics.

The sp_LogSpace stored procedure uses the DBCC SQLPERF statement to get the transaction log statistics. The procedure enters the DBCC SQLPERF data into a temporary table and extracts the log-size and space-used statistics, which it outputs to the varchar(20) string. The size is specified in megabytes (MB).

To use sp_LogSpace, you provide the database name as an input parameter. If you don’t provide this parameter, the current database name is used. For example, the code

USE northwind 
GO
EXEC sp_LogSpace 
EXEC sp_LogSpace 'pubs'
EXEC sp_LogSpace 'msdb' 

first uses sp_LogSpace to get the transaction log statistics for the current database (in this case, Northwind), then uses sp_LogSpace to get the transaction log statistics for the pubs and msdb databases.

I wrote and tested sp_LogSpace on a Windows XP machine running SQL Server 2000 Service Pack 1 (SP1). Figure 1 shows the results when I ran it on my machine.

Editor’s Note
Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $50.