Collect SQL Server database usage for free space and trending

By:   |   Comments (11)   |   Related: > Monitoring


I have over 80 instances hosting 800+ databases that I support on my own. The only way I can keep track of all these databases is to centralize my monitoring so I have a single point of focus on my environment. I can not spend my entire day (and night) hopping from SQL instance to SQL instance in SQL Server Management Studio. I am not the only DBA out there in this situation. So overworked brothers and sisters, what are we to do? We cook up our own monitoring solution that best fits our environments. This tip is the first in a series presenting the building blocks of such a solution. My first tip will focus on monitoring database files and their free space on a single SQL instance.


I find it important in my environment to monitor the following database/database file level metrics:

  • Server/Instance Name
  • Database Name
  • Database File Names (both logical and full physical path)
  • File Size (In Megabytes)
  • Database Status
  • Recovery Mode
  • Free Space (In Megabytes and Percent)

To collect this information I need to tap into the either the master.dbo.sysfiles system table in SQL 2000 or master.sys.sysfiles compatibility view in SQL 2005. I also need to make use of a few T-SQL functions at the DBA's disposal. First, let me present the query. Afterwards I'll explain the finer points.

ServerName VARCHAR(100), 
DatabaseName VARCHAR(100), 
FileSizeMB INT
LogicalFileName sysname
PhysicalFileName NVARCHAR(520), 
Status sysname
Updateability sysname
RecoveryMode sysname
FreeSpaceMB INT
FreeSpacePct VARCHAR(7), 
FreeSpacePages INT
PollDate datetime

DECLARE @command VARCHAR(5000

SELECT @command 'Use [' '?' '] SELECT 
@@servername as ServerName, 
'''' '?' '''' ' AS DatabaseName, 
CAST(sysfiles.size/128.0 AS int) AS FileSize, AS LogicalFileName, sysfiles.filename AS PhysicalFileName, 
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status, 
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability, 
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode, 
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(, ' 
'SpaceUsed' '''' ' ) AS int)/128.0 AS int) AS FreeSpaceMB, 
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(, 
'''' 'SpaceUsed' '''' ' ) AS int)/128.0)/(sysfiles.size/128.0)) 
AS decimal(4,2))) AS varchar(8)) + ' 
'''' '%' '''' ' AS FreeSpacePct, 
GETDATE() as PollDate FROM dbo.sysfiles' 
EXEC sp_MSForEachDB @command 


On my test system this produces the following results.
(Note:this was broken into 2 screenshots, so the output was not too wide for the webpage.)



There are quite a few calculations that may need explaining. Let's take a look at them as they appear in the query above:


This is the system variable that stores the name of the local SQL Server instance being run.

CAST(sysfiles.size/128.0 AS int) AS FileSize

Database size information is stored as 8Kb pages in sysfiles. If you have a 100Mb data file the value in sysfiles.size would be 12800 (100Mb * 1024) / 8. To reverse-engineer the size in Mb from a value that is stored as 8Kb pages you simply use the following formula:

File Size in Mb = (Pages In Kb) * (Kb per Mb) / (Kb per Page)

Using the information available to us (which is sysfiles.size) we can fill in the formula as thus: File Size in Mb = (sysfiles.size) * 1024/8. Since 1024/8 = 128 I've simplified the formula throughout the query as (sysfiles.size/128).

CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode

The DatabasePropertyEx() function returns the value for the database property or option specified. In this case we are interested in the Status, Updateability, and Recover database option values. I convert the values to the sysname datatype for compatibility with my metadata repository.

CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB

The FreeSpaceMB calculation is simple once you understand what I explained above for the calculations associated with FileSize. To determine free space we need to know how much of the total file space is being consumed. This information is exposed via the FILEPROPERTY() function. FILEPROPERTY() expects the following parameters: file name, and property.

Expect a future tip on the various uses for FILEPROPERTY(), but for now we focus on the SpaceUsed property. This value is also stored in 8Kb pages, so the factor of 1024/8 (or 128) remains constant. Using the same basic rules we discussed above this is what the formula would look like this: Available Space in Mb = (File Size in Mb) - (Space Used in Mb). The formula in the query casts all variables as integer data types and converts the available values from pages to Mb.

I know that one of the major foundations of database normalization is not storing calculated values in a database. However, I like to be able to trend my metadata over time and with the complexity of the formulas I prefer to do all my thinking up-front. That being said, I don't just store the size information in the table and run calculations in my queries after the fact - I do my calculations directly in the query that populates the repository. 

CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct

Free space as a percentage of total space is a simple variation of the previously stated formulas. It is based off of the following simplified formula and adjusted for Mb from the 8Kb pages value that is available to us: Free Space In Percent = 100 * (Free Space) / Total Space. I then present it as a formatted percentage.


In this example I am just displaying the results in real time and not storing the results to a permanent table. This could easily be changed by using a permanent table instead of a table variable and therefore you can do trending.

Next Steps
  • Tweak T-SQL code provided to fit your environment
  • Include this process in an SSIS package that can run against all your instances and dump metadata into a single database that you can report against.
  • Create a report that provides you with a listing of databases that are below a certain threshold of available space.
  • Stay tuned for more metadata monitoring tips.
  • Review previous tip on sp_MSForEachDB to see how to run the same query against multiple databases on a SQL instance.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, June 26, 2023 - 1:37:49 PM - Muhammad Tahir Back To Top (91342)
Thanks, it worked for me.

Thursday, October 15, 2015 - 10:32:16 AM - Mohan Aier Back To Top (38903)

Great work Tim - Much appreciated - Thanks for sharing



Thursday, August 21, 2014 - 11:03:31 AM - Chuck Fox Back To Top (34222)

Thank you for the code.  I have searched the web for the past 2 days and of all the approaches, I like this one the best.  Of couse, no elses approach does exactly what one wants.  In my case, I needed the code to be executed against other servers as well as the one to which I was connected.  I was looking for a permanent collection so I could track growth.  To that end, I turned the temp table into a permanent one and using the sys.servers table to drive a cursor, I was able to query all the linked servers. 



    ServerName          sysname,  

    DatabaseName        sysname,  

    FileSizeMB          int,  

    LogicalFileName     sysname,  

    PhysicalFileName    nvarchar(520),  

    DBStatus            sysname,  

    Updateability       sysname,  

    RecoveryMode        sysname,  

    FreeSpaceMB         int,  

    FreeSpacePct        decimal(5,4),  

    PollDate            datetime ) 



DECLARE @SrvName sysname

DECLARE @ServerList TABLE( ServerName sysname ) 

INSERT @ServerList

SELECT name FROM sys.servers --WHERE name IN ( @ServerNames ) 


DECLARE @Cmd varchar(MAX) = '

''USE [?]; 




    CAST( f.size / 128.0 AS int ),, f.filename,  




    CAST( f.size / 128.0 - CAST( FILEPROPERTY(, ''''SpaceUsed'''' ) AS int ) / 128.0 AS int ),  

    ( f.size/128.0 -CAST(FILEPROPERTY(, ''''SpaceUsed'''' ) AS int ) / 128.0 ) / ( f.size / 128.0 ), 



    dbo.sysfiles f;'''


DECLARE ReadServerList CURSOR FOR SELECT ServerName FROM @ServerList 

OPEN ReadServerList 


FETCH ReadServerList INTO @SrvName




    EXEC( 'EXEC [' + @SrvName + '].msdb.dbo.sp_MSforeachdb ' + @Cmd )

    FETCH ReadServerList INTO @SrvName


SELECT * FROM DBInfo ORDER BY ServerName, DatabaseName, LogicalFileName


Thursday, May 9, 2013 - 10:46:58 AM - Aswin Back To Top (23838)

Tim, thanks for the excellent use of Sp_MSforeachDB. 

Can you please let me know how can we use the same script if we have offlined databases. I think this script will only provide information about online databases. Your help will be greatly appericated :).



Tuesday, July 3, 2012 - 11:25:27 AM - Ivan Vuk Back To Top (18322)


This is  a brilliant. Exactly what I was trying to do but I was stuck as I did not know anything about the stored procedure sp_MSForEachDB.


Cool stuff. J

Thank you very much


Monday, January 16, 2012 - 3:15:39 AM - LL Back To Top (15658)

Is anyone getting this error (SQL 2008 R2 SP1 CU3)?

Msg 8115, Level 16, State 7, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Msg 8115, Level 16, State 7, Line 1
Arithmetic overflow error converting numeric to data type numeric.


Wednesday, June 9, 2010 - 5:47:55 PM - KiranAitha Back To Top (5676)

Hi ,

How can i use this script to query DB Usage of a Single database from 100+ servers.Basically i want to get the output into a single table on one of the servers where i run this script.

Kiran Aitha

Wednesday, January 6, 2010 - 3:54:01 PM - sqlchicken Back To Top (4666)

 Here is the query re-tooled to work with SQL 2000 and documented


/*Drop temporary table if exists*/
IF object_id('tempdb..#DBInfo') IS NOT NULL

/*Create a temporary table*/
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7), 
PollDate datetime)

/*Declare the SQL command to execute*/
DECLARE @command VARCHAR(5000)

/*SELECT (query) from variable*/
SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSizeMB, AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(, ' + '''' +
       'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles'

/*Insert values into temporary table*/

/*Scroll through each database and collect information and execute command variable query*/ 
EXEC sp_MSForEachDB @command

/*Get results from temporary table*/

/*Deletes temporary table*/

Tuesday, March 18, 2008 - 6:13:33 AM - rsbutterfly16 Back To Top (745)

hi can you put the steps of how to Include this process in an SSIS package that can run against all my instances/different servers and dump metadata into a single database that you can report against please.

Tuesday, February 5, 2008 - 12:48:34 PM - tews70 Back To Top (268)

Solved my own problem -

Changed table variable into a temporary table - works like a charm on 2000 now.

 Thank you for this very useful bit of code


Tuesday, February 5, 2008 - 12:15:55 PM - tews70 Back To Top (267)

This is very cool and exactly what I was trying to figure out - however I have SQL 2000 servers and get a message

Msg 197, Level 15, State 1, Line 45
EXECUTE cannot be used as a source when inserting into a table variable.

It works great on my SQL 2005 systems.

Is there an easy way to alter this code to work on 2000?

get free sql tips
agree to terms