Different ways to determine free space for SQL Server databases and database files

By:   |   Updated: 2024-05-22   |   Comments (12)   |   Related: 1 | 2 | 3 | 4 | 5 | More > Database Administration


Problem

One of the functions of a DBA is to keep an eye on free space within the database and database files. The auto grow feature is great as a last resort, but proactively managing the database files is a better approach. Also, this information can be used to shrink data files as needed if there is a lot of free space in the files.

In this tip we take a look at a few ways to find the current free space within a database, so you can better manage your database files.

Solution

There are a few ways to find out how much free space there is in your database. (Note that the size values go up and down, because of creating transactions, doing backups and shrinking files, so the exact same numbers are not in every example.)

To begin with we need to select a database and then get some information about the files.

USE Test5 
GO 

sp_helpdb Test5 
GO

If we run the above we will get the following output. Here we can see the total size for the database and also the current size of the data files, but this does not tell us how much free space there is.

sp_helpdb output

The next thing that we need to do is get information about each file that makes up the database. In the above result set we can see we have two names "Test5" and "Test5_log", which will be used for later steps. These are the logical names for the two files that make up the Test5 database. If the database had more than two files they would all be listed here.

1 - Using sp_spaceused to check free space in SQL Server

One option is to use sp_spaceused. If we run the following command we can see how much free space there is in the database, but this shows us total free space including the transaction log free space which may be totally misleading.

USE Test5 
GO 

sp_spaceused 

sp_spaceused output

2 - Using DBCC SQLPERF to check free space for a SQL Server database

Another option is to use the DBCC SQLPERF(logspace) command. This will give you output on just the log files for each database. Also, this gives you cumulative information, so if you have multiple log files this will show you the total free space across all log files for each database.

USE master 
GO 

DBCC SQLPERF(logspace) 

dbcc sqlperf logspace output

3 - Using DBCC SRHINKFILE to check free space for a SQL database

Another option is to use DBCC SHRINKFILE.

Important Note: the problem with this command is if you run this it will still shrink the files and this can take time and might not be something you want to do. There is no way to run this command to just get informational messages only.

USE Test5 
GO 

DBCC SHRINKFILE (test5) 
DBCC SHRINKFILE (test5_log) 

The result set for these two commands is shown below. The output is in 8K pages, so a simple way to calculate MB is to take the value and divide by 128. This would give us 393MB for the CurrentSize for FileId =1.

dbcc shrinkfile output

Here is the description of the output. One column that is helpful is the MinimumSize. This tells you how big you initially made the file when it was created. This is also the minimum value you could use if you want to use this information to shrink one of your files.

Column name Description
DbId Database identification number of the file the Database Engine tried to shrink.
FileId The file identification number of the file the Database Engine tried to shrink.
CurrentSize Number of 8-KB pages the file currently occupies.
MinimumSize Number of 8-KB pages the file could occupy, at minimum. This corresponds to the minimum size of originally created size of a file.
UsedPages Number of 8-KB pages currently used by the file.
EstimatedPages Number of 8-KB pages that the Database Engine estimates the file could be shrunk down to.

(source SQL Books Online)

As I mentioned, the downside is if there is free space in the file it will shrink the file even if we run the command with a much higher value for the file size. In this example, are log file is 682MB, but we are specifying a value of 10,000MB.

DBCC SHRINKFILE (test5_log, 10000)

4 - Using FILEPROPERTY to check for free space in a database

Another option is to use the SpaceUsed property of the FILEPROPERTY function which will tell us how much space is used and then we can calculate free space based on the current size and what is being used.

USE Test5 
GO 

SELECT DB_NAME() AS DbName, 
name AS FileName, 
size/128.0 AS CurrentSizeMB, 
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 

Here we can see the output from the above for the Test5 database.

sql server fileproperty output

Summary

As you can see there is no option that gives you all of the information you may want. The FILEPROPERTY option is probably the best, but the MinimumSize value from the DBCC SHRINKFILE would be nice to have as well. Now you have a few options for getting this information, so use the option that works best for your environment and the current issue you are dealing with.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

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

View all my tips


Article Last Updated: 2024-05-22

Comments For This Article




Monday, September 18, 2023 - 8:32:39 AM - Spencer Sullivan Back To Top (91574)
Excellent article. Thank you so much for the summary and information!

Thursday, January 17, 2019 - 1:46:37 PM - WIlson Back To Top (78805)

Hi Greg Robidoux

Thanks for your simple and straight to the point illustrations

Thay have been so helpful and worked exactly as explained


Sunday, March 5, 2017 - 12:18:03 PM - nikolz Back To Top (47293)

 I have 100 DBs on a server. No solution works for me.

 


Wednesday, July 13, 2016 - 2:07:30 AM - santosh Back To Top (41877)

 thanks 

 


Wednesday, January 8, 2014 - 7:46:54 AM - Karthik Back To Top (27989)

The below query gives results omitting information of few databases. Upon going through the results I found that the databases with is_cleanly_shutdown is 1 are missing. 

DBCC sqlperf (logspace)

Please throw some light on this.. 


Friday, October 4, 2013 - 6:04:53 AM - Mark Ma Back To Top (27034)

This is a good clear and complete instruction.


Wednesday, August 7, 2013 - 1:25:42 PM - Hasan Rahman Back To Top (26140)

 Hello, 

I am in a situation. We have a huze datawarehouse database need to shrink. Database size is now 11.2 TB and out of it 5.TB is unallocated space. My question is 

 We have only 1 TB space free on Data disk and Tempdb is on saparate drive. Do we need more disk space to perforn DBCC shrinkfile?

 

 

 


Friday, March 15, 2013 - 12:47:18 AM - DG Back To Top (22809)

Hi Greg,

below is the query am using to get the free space result.

But am getting the the result for the databse on which it is execute
I need the same kind of result,but for for all the databases on a instances
Could you please have a look in to this

SELECT  ds.name as filegroupname
, df.name AS 'FileName'
, physical_name AS 'PhysicalName'
, size/128 AS 'TotalSizeinMB'
, size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'AvailableSpaceInMB'
, CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'ActualSpaceUsedInMB'
, (CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0)/(size/128)*100. as '%SpaceUsed'
FROM sys.database_files df LEFT OUTER JOIN sys.data_spaces ds 
      ON df.data_space_id = ds.data_space_id;


Thursday, February 24, 2011 - 11:08:35 AM - Charlie Arehart Back To Top (13031)

You may want to add another "related entry" to this one:

http://www.mssqltips.com/tip.asp?tip=1178

where you show how to store the dbcc sqlperf(logspace) over time.

Thanks for all your wonderful tips, Greg and everyone else.


Thursday, July 30, 2009 - 7:59:39 AM - cchurchwell Back To Top (3815)
That does not recognize space under mount points as we have tried that and are having to write a CLR function using WMI to accomplish this.  I just thought there may be an easier way

Thursday, July 30, 2009 - 7:55:35 AM - admin Back To Top (3814)

Not 100% sure if this is what you need, but you can run this system stored procedure xp_fixeddrives to see how much space is left on all drives that SQL Server can see.

Take a look at these tips:

http://www.mssqltips.com/tip.asp?tip=1706

http://www.mssqltips.com/tip.asp?tip=1263

 


Thursday, July 30, 2009 - 5:46:56 AM - cchurchwell Back To Top (3813)

Do you know of any way to evaluate mount points free space on server you do not have RDP access to?















get free sql tips
agree to terms