Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Determine Free Space, Consumed Space and Total Space Allocated for SQL Server databases


By:   |   Read Comments (15)   |   Related Tips: More > Database Administration

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem
I've seen so many different options and scripts for determining free space, consumed space, and total space allocated for databases in Microsoft SQL Server.  Problem is none ever seem to give me all the information.  I need to run one script to see this information for the log file and a different one for the data files.  Is there anything out there that provides this information universally?

Solution
Ah, the classic and prevalent problem with most applications rears its ugly head in SQL Server as well: Consistency Fail.  Reader you are absolutely correct.  If we take a look at what you were referring to first I'll offer up a suggestion for a single-sourcing stored procedure for space consumption in your SQL Server databases.

Log File Space Metadata

DBCC SQLPERF

DBCC SQLPERF(logspace)  

DBCC SQLPERF(logspace) is an absolutely functional command if you are only interested in consumption of your database log files.  It provides the cumulative size for each log file for each database on the SQL Server instance as well as the amount of space consumed (as a percentage of total log file size).  A drawback is the fact that the results are an aggregate for the database.  If you have multiple log files the results are displayed at the database level, not at the file level.  While this DBCC command is handy when you are reviewing issues that arise out of inadequate log backup scheduling or incorrect log file sizing, it does not give you all the information you need to make informed decisions on sizing your log files, adjusting you backup schedule frequency or recovery model.

Data File Space Metadata

DBCC SHOWFILESTATS
DBCC SHOWFILESTATS WITH NO_INFOMSGS

This DBCC command provides us with similar, yet more-granular information about the data files for the current database.  Whereas DBCC SQLPERF(logspace) returns it's results in megabytes, DBCC SHOWFILESTATS presents its results in extents.  The inconsistency bus is making it's first stop here.  For those of you who may be newer to SQL Server, and there are plenty of you out there  - welcome to the party - an extent is made up of eight pages and each page is 8 KB in size.  Each page starts with a 96 byte header that stores internal metadata about the page (page number, page type, amount of free space, etc).  Therefore, if you need to convert results from extents to megabytes the following formulas are a good rule-of-thumb to tuck away for when you need it:

  • Page = 8Kb -----> Extent = 8 pages :: Extents = 64Kb
  • MB = 1024Kb :: MB = 16 Extents

Therefore, in the example above we would need to run a conversion from Extents to MBs and would see that the two data files are (32 * 16) or 512 MB in size.  We will be making use of these conversions later in this tip.

A Combined, Limited Solution

Most seasoned SQL Server DBAs have tapped into the system tables and views at one time or another.  The system tables were hidden as of SQL Server 2005, but in their place were views that presented the same information, under the same object naming conventions as their predecessors, the system tables.  The following code provides us with not only sizing information, but also auto growth settings.  All this is available via the sys.database_files system view in each database.

sys.Database_Files View

SELECT *,
   
[name]
   
physical_name
   
size,
   
type_desc,
   
growth,
   
max_size
FROM sys.database_files
ORDER BY [type]
[file_id]

The downside once again is the scope.  Since the sys.database_files view is stored within each database - the results are limited to the current database.  Furthermore, the units of measure are different within the confines of this table.  Auto growth settings are presented in KB, when the auto growth is based upon size, not percentage; the unit-of-measure for the files sizes are in extents.  The result set is confusing in regards to the auto growth increments.  It is impossible to discern whether the individual log and data files are going to grow as a percentage of file size or at a fixed value.  This problem is indicative of a problem with relying upon the system views.  For backward capacity purposes, we are forced into using view structures based upon the structures of system tables going back to initial versions of Microsoft SQL Server.  We SQL Server Professionals have been told for years not to rely upon the system tables for information because they may change in future releases.  However, the prevalence of tapping into these tables for various "home-grown" metadata and monitoring purposes put Microsoft into a box and forced them to support these out-dated or in most cases inadequate repository structures.  I am quite sure that the developers in Redmond know that they have inconsistently formatted or incomplete data in their tables that they are unable to modify.

An All-In-One Solution

That being said, I will also admit that I am part of the problem.  I love, absolutely LOVE digging into the system tables and views, as well as the dynamic management views and functions that were exposed with the release of SQL Server 2005.  I've utilized the strengths of the aforementioned code along with a few other tricks to come up with a stored procedure that can run against all databases on a SQL instance or a targeted database.  Please note that I have certain conventions I employ in my SQL Server environments.  I segregate all my manageability scripts inside a single database, called iDBA.  I also create a specific, dedicated schema for all my manageability objects: MetaBOT.  If you don't care to do so, please change the database and schema to conform to your standards.  As always the following disclaimer is in order: do not run code you receive from anyone against a production server without thorough testing first in a non-prod environment.  I'm trustworthy, but far from perfect.

The stored procedure accepts two parameters: @Granularity, for which the value should be either 'Database' or NULL; and @Database_Name, which as you would expect would refer to the name of a specific database on the server you are running the stored procedure against.  You will receive a different result set depending on which level of granularity you choose.  This stored procedure will create a temporary table (##Results) and will be populated through the use of a query against the sys.database_files table.  I also make use of the FILEPROPERTY() function to collect space consumption information that is not available via the sys.database_files object.  By utilizing the sp_MSforeachdb undocumented stored procedure in the master database I build and execute this query against all databases on the SQL Server and store the results in the ##Results temporary table.  Afterwards, depending upon the parameters passed into the stored procedure I format and return information to the end user.  The final step in the process is always proper cleanup; be sure to drop the ##Results temporary table.  Please review the code below making note of the items I've just discussed.  Afterwards we will examine what the results look like based upon different parameter combinations.  Don't be alarmed - there is a great deal of code, but it is really quite easy to follow.

Free Space, Consumed Space and Total Space Allocated Stored Procedure

USE iDBA  
GO  
CREATE PROCEDURE MetaBOT.usp_Sizing @Granularity VARCHAR(10NULL, @Database_Name sysname NULL AS  
DECLARE 
@SQL VARCHAR(5000)  

IF EXISTS (SELECT NAME FROM tempdb..sysobjects WHERE NAME '##Results')   
   
BEGIN   
       DROP TABLE 
##Results   
   
END  
     
CREATE TABLE 
##Results ([Database Name] sysname,
[File Name] sysname,
[Physical Name] NVARCHAR(260),
[File Type] VARCHAR(4),
[Total Size in Mb] 
INT,
[Available Space in Mb] 
INT,
[Growth Units] 
VARCHAR(15),
[Max File Size in Mb] 
INT)  

SELECT @SQL =   
'USE [?] INSERT INTO ##Results([Database Name], [File Name], [Physical Name],   
[File Type], [Total Size in Mb], [Available Space in Mb],   
[Growth Units], [Max File Size in Mb])   
SELECT 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 in Mb] =  
CASE ceiling([size]/128)   
WHEN 0 THEN 1  
ELSE ceiling([size]/128)  
END,  
[Available Space in 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*8/1024 AS varchar(20)) + ''Mb'''  
+  
       
'END,  
[Max File Size in Mb] =   
CASE [max_size]  
WHEN -1 THEN NULL  
WHEN 268435456 THEN NULL  
ELSE [max_size]  
END  
FROM sys.database_files  
ORDER BY [File Type], [file_id]'  

--Print the command to be issued against all databases  
PRINT @SQL  

--Run the command against each database  
EXEC sp_MSforeachdb @SQL  

--UPDATE ##Results SET [Free Space %] = [Available Space in Mb]/[Total Size in Mb] * 100  

--Return the Results  
--If @Database_Name is NULL:  
IF @Database_Name IS NULL  
   
BEGIN  
       IF 
@Granularity 'Database'  
           
BEGIN  
               SELECT   
              
T.[Database Name],  
              
T.[Total Size in Mb] AS [DB Size (Mb)],  
              
T.[Available Space in Mb] AS [DB Free (Mb)],  
              
T.[Consumed Space in Mb] AS [DB Used (Mb)],  
              
D.[Total Size in Mb] AS [Data Size (Mb)],  
              
D.[Available Space in Mb] AS [Data Free (Mb)],  
              
D.[Consumed Space in Mb] AS [Data Used (Mb)],  
              
CEILING(CAST(D.[Available Space in Mb] AS decimal(10,1))/D.[Total Size in Mb]*100AS [Data Free %],  
              
L.[Total Size in Mb] AS [Log Size (Mb)],  
              
L.[Available Space in Mb] AS [Log Free (Mb)],  
              
L.[Consumed Space in Mb] AS [Log Used (Mb)],  
              
CEILING(CAST(L.[Available Space in Mb] AS decimal(10,1))/L.[Total Size in Mb]*100AS [Log Free %]  
               
FROM   
                   
(  
                   
SELECT [Database Name],  
                       
SUM([Total Size in Mb]AS [Total Size in Mb],  
                       
SUM([Available Space in Mb]AS [Available Space in Mb],  
                       
SUM([Total Size in Mb]-[Available Space in Mb]AS [Consumed Space in Mb]   
                   
FROM ##Results  
                   
GROUP BY [Database Name]  
                   
AS T  
                   
INNER JOIN   
                   
(  
                   
SELECT [Database Name],  
                       
SUM([Total Size in Mb]AS [Total Size in Mb],  
                       
SUM([Available Space in Mb]AS [Available Space in Mb],  
                       
SUM([Total Size in Mb]-[Available Space in Mb]AS [Consumed Space in Mb]   
                   
FROM ##Results  
                   
WHERE ##Results.[File Type] 'Data'  
                   
GROUP BY [Database Name]  
                   
AS ON T.[Database Name] D.[Databasse Name]  
                   
INNER JOIN  
                   
(  
                   
SELECT [[Database Name],  
                       
SUM([Total Size in Mb]AS [Total Size in Mb],  
                       
SUM([Available Space in Mb]AS [Available Space in Mb],  
                       
SUM([Total Size in Mb]-[Available Space in Mb]AS [Consumed Space in Mb]   
                   
FROM ##Results  
                   
WHERE ##Results.[File Type] 'Log'  
                   
GROUP BY [Database Name]  
                   
AS ON T.[Database Name] L.[Database Name]  
               
ORDER BY D.[Database Name]  
           
END  
   ELSE  
       BEGIN  
           SELECT 
[Database Name],  
               
[File Name],  
               
[Physical Name],  
               
[File Type],  
               
[Total Size in Mb] AS [DB Size (Mb)],  
               
[Available Space in Mb] AS [DB Free (Mb)],  
               
CEILING(CAST([Available Space in Mb] AS decimal(10,1)) / [Total Size in Mb]*100AS [Free Space %],  
               
[Growth Units],  
               
[Max File Size in Mb] AS [Grow Max Size (Mb)]   
           
FROM ##Results   
       
END  
   END  

--Return the Results  
--If @Database_Name is provided  
ELSE  
   BEGIN  
       IF 
@Granularity 'Database'  
           
BEGIN  
               SELECT   
              
T.[Database Name],  
              
T.[Total Size in Mb] AS [DB Size (Mb)],  
              
T.[Available Space in Mb] AS [DB Free (Mb)],  
              
T.[Consumed Space in Mb] AS [DB Used (Mb)],  
              
D.[Total Size in Mb] AS [Data Size (Mb)],  
              
D.[Available Space in Mb] AS [Data Free (Mb)],  
              
D.[Consumed Space in Mb] AS [Data Used (Mb)],  
              
CEILING(CAST(D.[Available Space in Mb] AS decimal(10,1))/D.[Total Size in Mb]*100AS [Data Free %],  
              
L.[Total Size in Mb] AS [Log Size (Mb)],  
              
L.[Available Space in Mb] AS [Log Free (Mb)],  
              
L.[Consumed Space in Mb] AS [Log Used (Mb)],  
              
CEILING(CAST(L.[Available Space in Mb] AS decimal(10,1))/L.[Total Size in Mb]*100AS [Log Free %]  
               
FROM   
                   
(  
                   
SELECT [Database Name],  
                       
SUM([Total Size in Mb]AS [Total Size in Mb],  
                       
SUM([Available Space in Mb]AS [Available Space in Mb],  
                       
SUM([Total Size in Mb]-[Available Space in Mb]AS [Consumed Space in Mb]   
                   
FROM ##Results  
                   
WHERE [Database Name] @Database_Name  
                   
GROUP BY [Database Name]  
                   
AS T  
                   
INNER JOIN   
                   
(  
                   
SELECT [Database Name],  
                       
SUM([Total Size in Mb]AS [Total Size in Mb],  
                       
SUM([Available Space in Mb]AS [Available Space in Mb],  
                       
SUM([Total Size in Mb]-[Available Space in Mb]AS [Consumed Space in Mb]   
                   
FROM ##Results  
                   
WHERE ##Results.[File Type] 'Data'  
                       
AND [Database Name] @Database_Name  
                   
GROUP BY [Database Name]  
                   
AS ON T.[Database Name] D.[Database Name]  
                   
INNER JOIN  
                   
(  
                   
SELECT [Database Name],  
                       
SUM([Total Size in Mb]AS [Total Size in Mb],  
                       
SUM([Available Space in Mb]AS [Available Space in Mb],  
                       
SUM([Total Size in Mb]-[Available Space in Mb]AS [Consumed Space in Mb]   
                   
FROM ##Results  
                   
WHERE ##Results.[File Type] 'Log'  
                       
AND [Database Name] @Database_Name  
                   
GROUP BY [Database Name]  
                   
AS ON T.[Database Name] L.[Database Name]  
               
ORDER BY D.[Database Name]  
           
END  
       ELSE  
           BEGIN  
               SELECT 
[Database Name],  
              
[File Name],  
              
[Physical Name],  
              
[File Type],  
              
[Total Size in Mb] AS [DB Size (Mb)],  
              
[Available Space in Mb] AS [DB Free (Mb)],  
              
CEILING(CAST([Available Space in Mb] AS decimal(10,1))/[Total Size in Mb]*100AS [Free Space %],  
              
[Growth Units],  
              
[Max File Size in Mb] AS [Grow Max Size (Mb)]   
               
FROM ##Results   
               
WHERE [Database Name] @Database_Name  
           
END  
   END  
DROP TABLE 
##Results  

Examples

File-Level Granularity this option will provide you with information for each data and log file for every database on your SQL Server instance.  Specific metrics include naming information (logical and physical) in addition to file type, sizing, free space, and file auto growth information.  A NULL value for the Grow Max Size (MB) column simply means that there is no maximum size set for the specific file.  You will receive these results if you specify anything other than 'Database' for the @Granularity parameter (including NULL).

Example Execution 1
EXEC idba.MetaBOT.usp_Sizing

By specifying a value for the @Database_Name parameter we simply limit the results to a single database:

Example Execution 2

EXEC idba.MetaBOT.usp_Sizing NULL'Foo'  

Database-Level Granularity:  By passing 'Database' as the value for the @Granularity parameter, the results are slightly different in that the results are aggregated by database.  Since this is the case, metrics pertaining to growth are omitted.

Example Execution 3

EXEC idba.MetaBOT.usp_Sizing 'Database' 

Just as specifying a database name parameter value limited the results for the file granularity, so will specifying a database name at the database granularity:

Example Execution 4

EXEC idba.MetaBOT.usp_Sizing 'Database''Foo' 

One thing you will notice if you look at the results across any of the parameter options that you do not see in any of the native, stand-alone coding options is standardization of measurement units.  Size is always specified in megabytes.  Furthermore this process removes the need to run separate commands for log files and data files.  Finally, any of the number "crunching" you may perform to derive consumed space, free space, or percentages of those values based upon file size are all included.  If you care to go the extra step I recommend storing this information in a table so that you have a central source for space consumption trending.  You could easily modify the stored procedure code I gave you to place the results in a physical table in lieu of the ##Results temporary table.  I would recommend adding a date-based field if you do so in order to provide a valuable source for trending information.  Adding this stored procedure to a SQL Agent job run on a periodic, scheduled basis would add lightweight, standardized data collection.

Next Steps

  • Information on the undocumented stored procedure sp_MSforeachdb is available from MSSQLTips.com here.
  • In this previous MSSQLTips.com article I offered a different take on the process for reviewing consumption  The article you just read evolved from that process and highlights the flexibility that is available when querying system data.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, February 17, 2014 - 9:10:22 AM - Tom Pester Back To Top
A wonderful and essential script you have here

Strangely enough its the only one I could find. I was starting to build my own but this will more than do.

There are 2 faults in the script.


) AS D ON T.[Database Name] = D.[Databasse Name] -- First fault : Databa_ss_e
INNER JOIN
(
SELECT [[Database Name], -- Second fault : _[_[Database 

Do you still use this script or do the latter versions of Sql provide more readily solutions?

Thanks for the contribution!

Sunday, September 01, 2013 - 12:22:12 PM - Tim Ford Back To Top

Ray, sys.master_files doesn't cut it either.  No space consumption information to be retrieved from that system view.  It will give you size of the file, but not how much of that file is in use.

Are there some optimizations for the script?  Sure.  This is almost a 5 year old article.  However the query is not a heavy-hitter so if this was a Connect item I'd close it with no changes.  ;)

 

Thanks for you comments though.  It's nice to see people are still looking at this post after so many years.


Friday, April 05, 2013 - 11:17:49 AM - Ray Back To Top

All of the this information for all databases is located in Sys.Master_Files.  The table includes Database_id, data_space_id  and File_id to uniquely identify each file and assit linking to other views (e.g., sys.databases) to obtain additional information.

Accessing sys.master_files eliminates the need for dynamic SQL, using the undocumented stored procedure, creating a global temp table, etc.

If you use a conditional where clause you can get by with a single select.

Where

1 = Case when @dbName is null then 1

              when database_id = db_id(@dbName) then 1

              else 0

       End

 

 


Tuesday, March 20, 2012 - 2:22:17 AM - Shankar Walvekar Back To Top

Very useful script.

One more question,

How can we get size of a result set

 

e.g.,

If we want to know size of (select * from Employees) result set

Regards,

Shankar Walvekar

+91-9766117105


Wednesday, February 29, 2012 - 1:36:07 AM - sivaprasad Back To Top

Alternatively could have used sys.master_files ( instance wide view ) rather sys.database_files ( database wide view ) and it would avoid using of  sp_MSforeachdb 


Wednesday, February 29, 2012 - 1:16:06 AM - sivaprasad Back To Top

Good useful script.

This below part can be avoided; as  the type_desc  already have the file type description value

[File Type] =   
CASE type  
WHEN 0 THEN ''Data'''   
+  
           
'WHEN 1 THEN ''Log'''  
+  
       
'END,  

 


Monday, July 20, 2009 - 7:00:50 AM - admin Back To Top

This script has been replaced.


Saturday, July 18, 2009 - 6:11:22 AM - timmer26 Back To Top

You are correct in that growth, when measured in Mb, is stored as 8 Kb pages.  I've provided a fix to the editor and asked that the script be replaced.

 Thank you.


Friday, July 17, 2009 - 3:22:50 PM - jylintx Back To Top

correction: the unit of GROWTH column in sys.database_files is 8KB (same as unit of SIZE column), hence the GROWTH UNIT should be divided by 128 to convert to MB.


Friday, July 17, 2009 - 3:00:46 PM - jylintx Back To Top

I believe that the unit for GROWTH (sys.database_files) is 64K (equalt to an 'extent'), instead of MB, hence the GROWTH UNIT should be 'Extent' or it needs to be divided by 64 to convert to MB.

 

growth

int

0 = File is fixed size and will not grow.

>0 = File will grow automatically.

If is_percent_growth = 0, growth increment is in units of 8-KB pages, rounded to the nearest 64 KB.

If is_percent_growth = 1, growth increment is expressed as a whole number percentage.


Thursday, May 21, 2009 - 1:48:03 PM - garysjah Back To Top

 Thank you for the great article, Tim.

Just curious: without existence of permanent table(s) to hold historical stats for each db in an instance, is/are there system object(s) that can be queried to display historical stats (space usage/allocation) of databases within a SQL instance?

Many thanks in advance


Friday, January 09, 2009 - 8:01:25 AM - timmer26 Back To Top

Additionally, if dealing with larger data and log file sizes this script fails due to overflow errors.  Altering references to decimal(5,1) to decimal(10,1) resolves this issue.


Monday, November 24, 2008 - 8:22:53 AM - timmer26 Back To Top

One issue with this script was that it failed when run against instances where there were database names that included spaces.  I've sent a fix onto the editor to implement on 11/24/2008 and expect it to be fixed shortly.  Adding [ around the ? that is replaced with the db name resolves this issue.

 - Tim


Tuesday, November 18, 2008 - 11:46:18 AM - timmer26 Back To Top

Thanks Jerry.  I don't know about you, but the one thing I don't like about the reports in SSMS that came with SP2 was that if you have many DBs on the instance you're reporting against, many of the reports are not legible since the labels all overlap and blend together.  Makes it quite frustrating.  They are good as an initial point for reconfiguring to better fit your individual needs however.


Tuesday, November 18, 2008 - 9:33:47 AM - jerryhung Back To Top

Great script, thank you, I wish MSFT includes this by default

At least they have some built-in reports now in SSMS to show something (such as "Disk Usage" report per DB with fancy pie charts)

 

It even showed FILESTREAM files for SQL 2008

Database Name    File Name    Physical Name    File Type    DB Size (Mb)    DB Free (Mb)    Free Space %    Growth Units    Grow Max Size (Mb)
AdventureWorks2008    FileStreamDocuments    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents    NULL    1    NULL    NULL    0Mb    0


Learn more about SQL Server tools