SQL Query To Get Size and Free Space For All Databases

Inventory tracking and reporting is important for any Administrator position in IT. The DBA needs to be able to be able to report on the Database size requirements for all supported systems. The following script is not the only way this information can be collected but it is simple. We are grabbing the database size as well as the individual data file sizes and recovery information to report on.

Note: As a quick solution you can also use the Microsoft provided stored procedures to get a quick summary of the DB sizes.

EXEC sp_spaceused;

EXEC sp_helpdb;

SQL Query For Database Size and Free Space

The first step is to find a source for the Database information in our “master” DB. We will use the following system Views to get the database information as well as the file locations and their grow settings.

sys.master_files

sys.databases

From the sys.databases view we can grab a hand full of info about each database but here we are just interested in the ‘name’ and ‘recovery model’

SELECT [name], recovery_model_desc FROM sys.databases

Database Logical File Name

Each database can use many Physical files that also have an internal Logical file name. There is a many to one relationship where we have multiple files assigned to one database and each file has its own Physical and Logical name. When you create a new database you always have one data and one log file.
Using the following View we can see the databases, their physical file names and logical file names.

SELECT * FROM sys.master_files

Note: We use the World Wide Importers DB for this query as it has multiple files already defined.

Since the view does not have the Database name, just the Logical file name, the challenge here is to JOIN our first View with this one so we have the DB name and the recovery_model. By design the two Views have the “database_id” field in common so we can join on that and get everything together.

To save time on explanation, here is the SELECT with JOIN, aliasing and excluding the system databases. You will notice that we used a CASE statement for the “Autogrow” because the field can be a percent, string or number.

SELECT 
	db.[name] AS [DBName],
	db.recovery_model_desc,
	files.[name] AS [File_Name],
	files.[type_desc],
	files.physical_name,
	CONVERT(DECIMAL(10,2), files.SIZE/128.0) AS [FILESIZE_MB],
	files.state_desc,
	CASE is_percent_growth 
		WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -'
			ELSE '' END 
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
			ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB'
			END 
        + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]'
			ELSE ''
			END AS [AutoGrow]

FROM sys.master_files AS files
INNER JOIN sys.databases AS db
	ON files.database_id = db.database_id
WHERE db.[name] NOT IN ('model','tempdb','msdb','master');

Basically this query quickly displays what can be see in the DB properties window in SSMS. What is missing are the details for each of the file such as their file sizes.
Unfortunately the details on these files is stored in the individual databases so we cannot just join some system Views to get it.

Within each database is the “sys.database_files” view that contains the details for each file inside the DB. To see this for each DB you will need to specify the DB using the “USE” statement.

USE [WorldWideImporters];
SELECT * FROM sys.database_files

Using sp_msforeachdb

We will be using the undocumented and unsupported stored procedure called “sp_foreachdb” as this allows me to iterate through the DB’s and execute in the database context. This is what we need to grab the sys.database_files data and JOIN it with a previous query.

EXEC sp_msforeachdb '
USE [?];
SELECT * FROM sys.database_files
'

Temp Table To Store Results

Another challenge we have is that the sp_msforeachdb procedure executes for each DB and a new transaction for the next. That means we cannot use a Temp variable to store our data (dbtales com) while it iterates through each database.

The answer we decided on was to create a simple Temp table in TempDB, insert the database info from our first query and then update the temp table with the file details inside the sp_msforeachdb procedure.

IF OBJECT_ID(‘tempdb..#DBFiles’) IS NOT NULL
DROP TABLE #DBFiles

IF OBJECT_ID('tempdb..#DBFiles') IS NOT NULL
    DROP TABLE #DBFiles

CREATE TABLE #DBFiles
(
	[id] INT IDENTITY(1,1), 
	[DBName] VARCHAR(200),
	[Recovery] VARCHAR(200) NULL,
	[File_Name] VARCHAR(200) NULL,
	[Type_Desc] VARCHAR(20) NULL,
	[physical_name] VARCHAR(MAX) NULL,
	[FileSize_MB] DECIMAL(10,2) NULL,
	[UsedSpace_MB] DECIMAL(10,2) NULL,
	[FreeSpace_MB] DECIMAL(10,2) NULL,
	[FreeSpace_%] DECIMAL(10,2) NULL,
	[State_Desc] VARCHAR(20) NULL,
	[AutoGrow] VARCHAR(200) NULL
)

For Each Database

With our Temp table populated we can now iterate through all of the DB’s, excluding the system DB, and update our temp table with the additional file data were looking for.

EXEC sp_msforeachdb '
USE [?];

IF (DB_name() NOT IN (''model'',''tempdb'',''msdb'',''master''))
BEGIN
  UPDATE #DBFiles
	SET [USEDSPACE_MB] = CONVERT(DECIMAL(10,2), SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(dbf.[name], ''SpaceUsed'') AS INT)/128.0)),
	[FREESPACE_MB] = CONVERT(DECIMAL(10,2), dbf.SIZE/128.0 - CAST(FILEPROPERTY(dbf.NAME, ''SpaceUsed'') AS INT)/128.0),
	[FREESPACE_%] = CONVERT(DECIMAL(10,2), ((dbf.SIZE/128.0 - CAST(FILEPROPERTY(dbf.NAME, ''SpaceUsed'') AS INT)/128.0)/(dbf.SIZE/128.0))*100)
FROM #DBFiles AS temp INNER JOIN sys.database_files AS dbf 
	ON temp.[physical_name] = dbf.[physical_name] COLLATE DATABASE_DEFAULT

END '

SQL Query To Get Size and Free Space For All Databases Code

IF OBJECT_ID('tempdb..#DBFiles') IS NOT NULL
    DROP TABLE #DBFiles

CREATE TABLE #DBFiles
(
	[id] INT IDENTITY(1,1), 
	[DBName] VARCHAR(200),
	[Recovery] VARCHAR(200) NULL,
	[File_Name] VARCHAR(200) NULL,
	[Type_Desc] VARCHAR(20) NULL,
	[physical_name] VARCHAR(MAX) NULL,
	[FileSize_MB] DECIMAL(10,2) NULL,
	[UsedSpace_MB] DECIMAL(10,2) NULL,
	[FreeSpace_MB] DECIMAL(10,2) NULL,
	[FreeSpace_%] DECIMAL(10,2) NULL,
	[State_Desc] VARCHAR(20) NULL,
	[AutoGrow] VARCHAR(200) NULL
)

INSERT INTO #DBFiles
SELECT 
	db.[name] AS [DBName],
	db.recovery_model_desc,
	files.[name] AS [File_Name],
	files.[type_desc],
	files.physical_name,
	CONVERT(DECIMAL(10,2), files.SIZE/128.0) AS [FILESIZE_MB],
	NULL AS [UsedSpace_MB],
	NULL AS [FreeSpace_MB],
	NULL AS [FreeSpace_%],
	files.state_desc,
	CASE is_percent_growth 
		WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -'
			ELSE '' END 
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
			ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB'
			END 
        + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting! - dbtales.com]'
			ELSE ''
			END AS [AutoGrow]

FROM sys.master_files AS files
INNER JOIN sys.databases AS db
	ON files.database_id = db.database_id
WHERE db.[name] NOT IN ('model','tempdb','msdb','master');

EXEC sp_msforeachdb '
USE [?];

UPDATE #DBFiles
	SET [USEDSPACE_MB] = CONVERT(DECIMAL(10,2), SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(dbf.[name], ''SpaceUsed'') AS INT)/128.0)),
	[FREESPACE_MB] = CONVERT(DECIMAL(10,2), dbf.SIZE/128.0 - CAST(FILEPROPERTY(dbf.NAME, ''SpaceUsed'') AS INT)/128.0),
	[FREESPACE_%] = CONVERT(DECIMAL(10,2), ((dbf.SIZE/128.0 - CAST(FILEPROPERTY(dbf.NAME, ''SpaceUsed'') AS INT)/128.0)/(dbf.SIZE/128.0))*100)
FROM #DBFiles AS temp
	INNER JOIN sys.database_files AS dbf 
	ON temp.[physical_name] = dbf.[physical_name] COLLATE DATABASE_DEFAULT '

SELECT * FROM #DBFiles ORDER BY DBName

---