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

 

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


By:   |   Read Comments (10)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Database Administration

Attend these FREE MSSQLTips webcasts >> click to register


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. The problem with this command is if you run this it will still shrink the files. 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 2005 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


Last Update:


signup button

next tip button



About the author





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     



Sunday, March 05, 2017 - 12:18:03 PM - nikolz Back To Top

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

 


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

 thanks 

 


Wednesday, January 08, 2014 - 7:46:54 AM - Karthik Back To Top

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 04, 2013 - 6:04:53 AM - Mark Ma Back To Top

This is a good clear and complete instruction.


Wednesday, August 07, 2013 - 1:25:42 PM - Hasan Rahman Back To Top

 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

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

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

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

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


Learn more about SQL Server tools