Quickly Identify Database and File Sizing for Any SQL Server Instance

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]
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;

 

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.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish