Quickly Identify Database and File Sizing for Any SQL Server Instance

I frequently find myself needing to see the sizing information for my SQL Server databases or their files. I also need to periodically check to ensure I've the right auto grow settings enabled to meet best practices when I engage with a new client. I've created a single script that does this and I want to share it with my readers.

Tim Ford, Owner

March 2, 2017

8 Min Read
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:

  • Which databases are my biggest consumers of storage?

  • Are any of my databases' files using percentage as a measure of growth for auto-grow events?

  • Am I seeing any situations where my transaction logs are sized incorrectly for the size of my database?

  • What are my largest transaction logs?

  • Are the data files for tempdb sized the same and with the same growth settings?

  • Am I getting close to filling up any of my data files or log files?

  • Are my data (or log) files hosted on an incorrect drive?

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:

  • Server Name

  • Database Name

  • File Name (both logical and physical)

  • File Type

  • File Size, Consumption, Free Space

  • Growth Units (percent or a fixed mb size)

  • Maximum File Size

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:

  • server

  • database_name

  • db_size_mb

  • db_free_mb

  • db_used_mb

  • data_size_mb

  • data_free_mb

  • data_used_mb

  • data_used_pct

  • log_size_mb

  • log_free_mb

  • log_used_mb

  • log_used_pct

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

  • server

  • database_name

  • file_name

  • physical_name

  • file_type

  • db_size_mb

  • db_free_mb

  • db_used_mb

  • free_space_pct

  • growth_units

  • max_file_size_mb

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;

 

 logo in a gray background |

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

EXEC dbo.sp_sizing 'd', NULL

 

 logo in a gray background |

>

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

EXEC dbo.sp_sizing 'd', 'master'

 

 logo in a gray background |

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

EXEC dbo.sp_sizing NULL, 'master'

 

 logo in a gray background |

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.

About the Author

Tim Ford

Owner, SQL Cruise

http://thesqlagentman.com/

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like