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:
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.