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: <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]
GO

CREATE 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.com
Use 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;

 

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

                              EXEC dbo.sp_sizing 'd', NULL

 

>

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

                              EXEC dbo.sp_sizing 'd', 'master'

 

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

                              EXEC dbo.sp_sizing NULL, 'master'

 

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.