Home
About me and Nucleus
Partners
Articles, tips etc.
My blog
Links
Microsoft articles
Forums
Course demo files
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:
| value | meaning |
| 'n' | database name (default) |
| 'd' | data space allocated |
| 'l' | log space allocated |
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_name | data_allocated | data_used | log_allocated | log_used |
| Adventureworks2008 | 180 | 179 | 1 | 0 |
| Credit | 26 | 24 | 3 | 0 |
| master | 4 | 2 | 1 | 0 |
| Utilities | 3 | 1 | 1 | 0 |
| [ALL] | 247 | 234 | 38 | 7 |
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_name, log_size, log_percentage_used, status_)
EXECUTE (@sql)
----Populate data space usage
DECLARE db CURSOR FOR SELECT name FROM master..sysdatabases WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
OPEN db
WHILE 1 = 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_group, total_extents, used_extents, name_, 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_name, data_allocated, data_used, log_allocated, log_used, is_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_name) AS is_sum
FROM (
SELECT database_name, SUM(total_extents) AS data_alloc, SUM(used_extents) AS 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_name, data_allocated, data_used, log_allocated, log_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.