Nucleus Datakonsult sp_db_space_usage - show space usage for all databases

Home

About me and Nucleus
Partners

Articles, tips etc.
My blog
Links
Microsoft articles
Forums

Course demo files




Microsoft Most Valuable Professional


Overview
One of the first things I want to do when I look at a new SQL Server is to get an idea of space usage details for each database, as well as total.

Versions etc.
This script should work with SQL Server 2000 and more recent versions.
Article written: 2009-12-29
Article last modified: 2010-05-24

Details
The procedure returns a row for each database plus one row for all databases. You get the name of the database, data space allocated, data space used, log space allocated and log space used. The procedure accepts one parameter (@sort), which is used for sorting the result. The value is a single letter:
valuemeaning
'n'database name (default)
'd'data space allocated
'l'log space allocated
All units are MB.

Usage examples
EXEC sp_db_space_usage
EXEC sp_db_space_usage 'n'
EXEC sp_db_space_usage 'd'
EXEC sp_db_space_usage 'l'


Example result (shortened)
database_namedata_allocateddata_usedlog_allocatedlog_used
Adventureworks200818017910
Credit262430
master4210
Utilities3110
[ALL]247234387

The code:
USE master 
GO 

IF OBJECT_ID('sp_db_space_usage'IS NOT NULL DROP PROC sp_db_space_usage 
GO 

CREATE PROC sp_db_space_usage 
@sort CHAR(1'n' 
AS 
/********************************************************************* 
@sort accept three values: 'n' (default), 'd' and 'l'. 
It specifies the sort order (name, data allocated, log allocated). 

Written by Tibor Karaszi 2009-12-29 
Modified 2010-01-19, fixed data type for db name. Thanks csm! 
Modified 2010-05-24, added support for offline databases.  
  Thanks Per-Ivan N?und. 
*********************************************************************/  
SET NOCOUNT ON 
DECLARE 
@sql NVARCHAR(2000
DECLARE @db_name sysname 

--Create tables to hold space usage stats from commands 
CREATE TABLE #dbcc_sqlperf_logspace 

database_name sysname NOT NULL 
,
log_size real NOT NULL 
,
log_percentage_used real NOT NULL 
,
status_ INT NOT NULL 


CREATE TABLE #dbcc_showfilestats 

database_name sysname NULL 
,
file_id_ INT NOT NULL 
,
file_group INT NOT NULL 
,
total_extents bigint NOT NULL 
,
used_extents bigint NOT NULL 
,
name_ sysname NOT NULL 
,
file_name_ NVARCHAR(3000) NOT NULL 


--Create table to hold final output 
CREATE TABLE #final_output 

database_name sysname 
,data_allocated INT 
,data_used INT 
,log_allocated INT 
,log_used INT 
,is_sum bit 


--Populate log space usage  
SELECT @sql 'DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'  
INSERT INTO #dbcc_sqlperf_logspace(database_namelog_sizelog_percentage_usedstatus_)  
EXECUTE (@sql)  

----Populate data space usage  
DECLARE db CURSOR FOR SELECT name FROM master..sysdatabases WHERE DATABASEPROPERTYEX(name'Status''ONLINE' 
OPEN db  
WHILE 1  
BEGIN  
FETCH 
NEXT FROM db INTO @db_name  
IF @@FETCH_STATUS <> 0  
BREAK  
SET 
@sql 'USE ' QUOTENAME(@db_name) + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS'  
INSERT INTO #dbcc_showfilestats(file_id_file_grouptotal_extentsused_extentsname_file_name_)  
EXEC (@sql)  
UPDATE #dbcc_showfilestats SET database_name @db_name WHERE database_name IS NULL  
END  
CLOSE 
db  
DEALLOCATE db  

--Result into final table  
INSERT INTO #final_output(database_namedata_allocateddata_usedlog_allocatedlog_usedis_sum)  
SELECT  
CASE WHEN d.database_name IS NOT NULL THEN d.database_name ELSE '[ALL]' END AS database_name  
,SUM(CAST((d.data_alloc 64.00) / 1024 AS decimal(18,2))) AS data_alloc  
,SUM(CAST((d.data_used 64.00) / 1024 AS decimal(18,2))) AS data_used  
,SUM(CAST(log_size AS numeric(18,2))) AS log_size  
,SUM(CAST(log_percentage_used 0.01 log_size AS numeric(18,2))) AS log_used  
,GROUPING(d.database_nameAS is_sum  
FROM (  
SELECT database_nameSUM(total_extentsAS data_allocSUM(used_extentsAS data_used  
FROM #dbcc_showfilestats  
GROUP BY database_name  
AS d  
INNER JOIN #dbcc_sqlperf_logspace AS l  
ON d.database_name l.database_name  
GROUP BY d.database_name WITH ROLLUP  

--Output result  
SELECT database_namedata_allocateddata_usedlog_allocatedlog_used  
FROM #final_output  
ORDER BY  
is_sum  
,CASE WHEN @sort 'n' THEN database_name END  
,CASE WHEN @sort 'd' THEN data_allocated END DESC  
,CASE WHEN @sort 'l' THEN log_allocated END DESC  

--Test execution  
/*  
EXEC sp_db_space_usage  
EXEC sp_db_space_usage 'n'  
EXEC sp_db_space_usage 'd'  
EXEC sp_db_space_usage 'l'  
*/  
GO  
EXEC sp_MS_Marksystemobject 'sp_db_space_usage' 


Change log
See the comments inside the proc code.