Home
About me and Nucleus
Partners
Articles, tips etc.
My blog
Links
Microsoft articles
Forums
Courses and training
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.
See comment block in procedure source code for version history of the procedure.
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 |
| 'r' | rollup information only |
Usage examples
EXEC sp_dbinfo
EXEC sp_dbinfo 'n'
EXEC sp_dbinfo 'd'
EXEC sp_dbinfo 'l'
Example result
| 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_dbinfo') IS NOT NULL DROP PROC sp_dbinfo
GO
CREATE PROC sp_dbinfo
@sort char(1) = 'n'
AS
/***************************************************************************
@sort accept 4 values: 'n' (default), 'd', 'l' and 'r'.
It specifies the sort order (name, data allocated, log allocated, rollup only).
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.
Modified 2011-07-21, SQL Server 11, use sysperfinfo instead of DBCC SQLPERF.
Modified 2011-09-23, master instead of MASTER, also qualified sysperfinfo.
Modified 2011-12-28, renamed to sp_dbinfo, added rollup option.
***************************************************************************/
SET NOCOUNT ON
DECLARE @sql nvarchar(2000)
DECLARE @db_name sysname
--Create tables to hold space usage stats from commands
CREATE TABLE #logspace
(
database_name sysname NOT NULL
,log_size real NOT NULL
,log_percentage_used real 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
INSERT INTO #logspace(database_name, log_size, log_percentage_used)
SELECT
instance_name AS 'Database Name'
,MAX(CASE
WHEN counter_name = 'Log File(s) Size (KB)' THEN cntr_value / 1024.
ELSE 0
END) AS 'Log Size (MB)'
,MAX(CASE
WHEN counter_name = 'Percent Log Used' THEN cntr_value
ELSE 0
END) AS 'Log Space Used (%)'
FROM master..sysperfinfo
WHERE counter_name IN('Log File(s) Size (KB)', 'Percent Log Used')
AND instance_name != '_total'
GROUP BY instance_name
----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_allocated
,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_allocated
,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 #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
WHERE database_name = '[ALL]' OR @sort <> 'r'
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_dbinfo
EXEC sp_dbinfo 'n'
EXEC sp_dbinfo 'd'
EXEC sp_dbinfo 'l'
EXEC sp_dbinfo 'r'
*/
GO
EXEC sp_MS_Marksystemobject 'sp_dbinfo'