Quickly Identify Database and File Sizing for Any SQL Server Instance

As a Database Administrator I frequently encounter the need to look into the sizes of my databases at both the database level (aggregating the log file and all the data files that comprise the larger database) or the file level. Sometimes I need this information for all the databases and sometimes just a single database. While there are options to collect this information there is not a single pane of glass that exposes all the metadata points that are of interest to me. Why do I care? When will I need this information? Let's consider the questions that can be answered with this sort of information:

I can go into SQL Server Management Studio (SSMS) and expose the database properties and see information for the individual files: their creation size, logical and physical names, and auto grow settings but I can't see how "full" those individual files are currently.

I can query sys.database_files and get similar information but still miss out on those missing data points of file utilization and I'm still limited to only looking at the results for a single database.

Likewise I can query the sys.master_files system view and get the identical information presented in sys.database_files but instead of getting results for just a single database the information is returned for every database on the SQL Server instance. You still miss out on that important metric of space used - which also means you're oblivious to how much space remains in a file before an auto-grow event occurs (if auto-growth is enabled for the file.)

You can utilize t-sql to query for the space utilization in a file through the FILEPROPERTY() function but then you need to join it into the results from other options I've mentioned earlier.

The other thing to consider is that all this information that's returned through these various means don't necessarily match in terms of units of measure: some of this information is returned as megabytes (mb) some as counts of data pages (which are 8 kilobytes (kb) each.) The disparity does not do us much good.

All this leads to the need to have a single option to go to in order to return information that provides data points that are valuable to the Database Administrator:

Since there is no single option for aggregating this information together I had to build a stored procedure to collect it for me and it's time to share it with all of you.

Parameters

The underlying code taps into some of those sources I mentioned above: sys.database_files and the FILEPROPERTY() function. The remainder of it is pure math and converting 8kb pages (in some cases) into a megabyte unit. The stored procedure accepts two parameters:

@granularity: d | NULL - 'd' signifies a database level granularity and aggregates all size metrics for each data file (and separately the transaction log) into a single row.

@database_name: | NULL - if a database name is specified then only the results for that database are returned. Otherwise results for all databases on the instance are returned.

Returned Results

The following columns are returned when specifying a database-level of granularity:

The following columns are returned when specifying a file-level of granularity:

The Stored Procedure Code

What follows is the code for this procedure. As with any code you download from the internet vet it's content and performance in a non-production environment before executing against production. That being said it is a read-only command so you'll not affect the values or structure of any data on your systems but I always advocate for understanding any code you run from outside sources (even me) against production:

USE [master]GOCREATE PROCEDURE [dbo].[sp_sizing] @granularity varchar(1) = NULL, @database_name sysname = NULL AS/*-------------------------------------------------------------dbo.sp_sizing Stored Procedure Created by Tim Ford, www.sqlcruise.com, www.thesqlagentman.comUse freely but review code before executing.Code downloaded from internet so execute at your own risk.-------------------------------------------------------------*/DECLARE @sql_command VARCHAR(5000) CREATE TABLE #Results ([server] NVARCHAR(128), [database_name] NVARCHAR(128), [file_name] NVARCHAR(128), [physical_name] NVARCHAR(260),     [file_type] VARCHAR(4), [total_size_mb] INT, [available_space_mb] INT, [growth_units] VARCHAR(15), [max_file_size_mb] INT) SELECT @sql_command =  'USE [?] INSERT INTO #Results([server], [database_name], [file_name], [physical_name],  [file_type], [total_size_mb], [available_space_mb],  [growth_units], [max_file_size_mb])  SELECT CONVERT(nvarchar(128), SERVERPROPERTY(''Servername'')), DB_NAME(), [name] AS [file_name],  physical_name AS [physical_name],  [file_type] =  CASE type WHEN 0 THEN ''Data'''  +        'WHEN 1 THEN ''Log''' +    'END, [total_size_mb] = CASE ceiling([size]/128)  WHEN 0 THEN 1 ELSE ceiling([size]/128) END, [available_space_mb] =  CASE ceiling([size]/128) WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128) ELSE (([size]/128) - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128) END, [growth_units]  =  CASE [is_percent_growth]  WHEN 1 THEN CAST([growth] AS varchar(20)) + ''%''' +        'ELSE CAST([growth]/1024*8 AS varchar(20)) + ''Mb''' +    'END, [max_file_size_mb] =  CASE [max_size] WHEN -1 THEN NULL WHEN 268435456 THEN NULL ELSE [max_size]/1024*8 END FROM sys.database_files WITH (NOLOCK)ORDER BY [file_type], [file_id]' --Print the command to be issued against all databases --PRINT @sql_command --========================================--RUN COMMAND AGAINST EACH DATABASE--========================================EXEC sp_MSforeachdb @sql_command --=================================--RETURN THE RESULTS--If @database_name is NULL: --=================================IF @database_name IS NULL    BEGIN    IF @granularity= 'd' /* Database Scope */       BEGIN        SELECT             T.[server], T.[database_name],            T.[total_size_mb] AS [db_size_mb],            T.[available_space_mb] AS [db_free_mb],            T.[used_space_mb] AS [db_used_mb],            D.[total_size_mb] AS [data_size_mb],            D.[available_space_mb] AS [data_free_mb],            D.[used_space_mb] AS [data_used_mb],            CEILING(CAST(D.[available_space_mb] AS decimal(10,1)) / D.[total_size_mb]*100) AS [data_free_pct],           L.[total_size_mb] AS [log_size_mb],            L.[available_space_mb] AS [log_free_mb],            L.[used_space_mb] AS [log_used_mb],            CEILING(CAST(L.[available_space_mb] AS decimal(10,1)) / L.[total_size_mb]*100) AS [log_free_pct]        FROM             (    SELECT [server], [database_name],    SUM([total_size_mb]) AS [total_size_mb],    SUM([available_space_mb]) AS [available_space_mb],    SUM([total_size_mb]-[available_space_mb]) AS [used_space_mb]     FROM #Results    GROUP BY [server], [database_name]            ) AS T            INNER JOIN     (    SELECT [server], [database_name], SUM([total_size_mb]) AS [total_size_mb], SUM([available_space_mb]) AS [available_space_mb], SUM([total_size_mb]-[available_space_mb]) AS [used_space_mb]     FROM #Results    WHERE #Results.[file_type] = 'Data'    GROUP BY [server], [database_name]    ) AS D ON T.[database_name] = D.[database_name]            INNER JOIN    (    SELECT [server],[database_name], SUM([total_size_mb]) AS [total_size_mb], SUM([available_space_mb]) AS [available_space_mb], SUM([total_size_mb]-[available_space_mb]) AS [used_space_mb]     FROM #Results    WHERE #Results.[file_type] = 'Log'    GROUP BY [server], [database_name]    ) AS L ON T.[database_name] = L.[database_name]        ORDER BY D.[database_name]        END ELSE /* File Scope */BEGIN SELECT [server], [database_name], [file_name], [physical_name], [file_type], [total_size_mb] AS [db_size_mb], [available_space_mb] AS [db_free_mb], CEILING(CAST([available_space_mb] AS DECIMAL(10,1)) / [total_size_mb]*100) AS [free_space_pct], [growth_units], [max_file_size_mb] /* AS [Grow Max Size (Mb)]  */FROM #Results  ORDER BY database_name, file_type, [file_name]END END --=================================--RETURN THE RESULTS FOR A DATABASE--If @database_name is provided: --=================================ELSE    BEGIN    IF @granularity= 'd' /* Database Scope */       BEGIN        SELECT T.[server],T.[database_name], T.[total_size_mb] AS [db_size_mb], T.[available_space_mb] AS [db_free_mb], T.[used_space_mb] AS [db_used_mb], D.[total_size_mb] AS [data_size_mb], D.[available_space_mb] AS [data_free_mb], D.[used_space_mb] AS [data_used_mb], CEILING(CAST(D.[available_space_mb] AS DECIMAL(10,1)) / D.[total_size_mb]*100) AS [data_free_pct], L.[total_size_mb] AS [log_size_mb], L.[available_space_mb] AS [log_free_mb], L.[used_space_mb] AS [log_used_mb], CEILING(CAST(L.[available_space_mb] AS DECIMAL(10,1)) / L.[total_size_mb]*100) AS [log_free_pct]        FROM             (            SELECT [server], [database_name],            SUM([total_size_mb]) AS [total_size_mb],            SUM([available_space_mb]) AS [available_space_mb],            SUM([total_size_mb]-[available_space_mb]) AS [used_space_mb]             FROM #Results            WHERE [database_name] = @database_name            GROUP BY [server], [database_name]            ) AS T            INNER JOIN     (    SELECT [server], [database_name],    SUM([total_size_mb]) AS [total_size_mb],    SUM([available_space_mb]) AS [available_space_mb],    SUM([total_size_mb]-[available_space_mb]) AS [used_space_mb]     FROM #Results    WHERE #Results.[file_type] = 'Data'    AND [database_name] = @database_name    GROUP BY [server], [database_name]    ) AS D ON T.[database_name] = D.[database_name]            INNER JOIN    (    SELECT [server], [database_name],    SUM([total_size_mb]) AS [total_size_mb],    SUM([available_space_mb]) AS [available_space_mb],    SUM([total_size_mb]-[available_space_mb]) AS [used_space_mb]     FROM #Results    WHERE #Results.[file_type] = 'Log'    AND [database_name] = @database_name    GROUP BY [server], [database_name]    ) AS L ON T.[database_name] = L.[database_name]        ORDER BY D.[database_name]        END    ELSE /* File Scope */       BEGIN        SELECT [server],[database_name], [file_name], [physical_name], [file_type], [total_size_mb] AS [db_size_mb], [available_space_mb] AS [db_free_mb], CEILING(CAST([available_space_mb] AS DECIMAL(10,1)) / [total_size_mb]*100) AS [free_space_pct], [growth_units], [max_file_size_mb] /* AS [Grow Max Size (Mb)]  */       FROM #Results         WHERE [database_name] = @database_name  ORDER BY file_type, [file_name]       END    END GO

Sample Results

Sample results are provided below based upon the four possible combinations of parameter settings.

Default Parameters: file level granularity and return results for all databases

EXEC dbo.sp_sizing;

Fig. 1: subset of returned results for default parameters

Parameters: specifying database level granularity and return results for all databases

EXEC dbo.sp_sizing 'd', NULL

Fig. 2: Subset of results for database granularity and no filter for a specific database

>

Parameters: database level granularity and return results for only a single database specifying database

EXEC dbo.sp_sizing 'd', 'master'

Fig 3: results for database granularity filtering for just master database

Parameters: file level granularity and return results for only a single database specifying database

EXEC dbo.sp_sizing NULL, 'master'

Fig 4: file granularity filtering for only the master database

Conclusion

What this script allows a DBA to do is rapidly determined the state of files and databases for answering a variety of questions: both those stated at the start of the article as well as others that come at us out of nowhere on a regular basis. Hopefully you'll find as much value for this as I have. One more tool in your pocket providing you with a solution to give accurate answers fast to those that need it.