Script to find amount of free space in datafiles

datafileDatafiles in SQL Server do not grow constantly while data is inserted. They grow incrementally. If there is no more unallocated space in a datafile, SQL Server tries to add a new extent to it (for example 200 MB of free disk space). Of course it works if autogrowth is enabled and a current datafile size has not reached maxsize.

Because of this whole process, the datafiles are usually not fully filled with data.

If you disable autogrowth for your datafiles, then you should take care of managing available space yourself. It can be done by adding a new datafile or extending one of existing ones. Either way, you may need to identify which datafiles have almost no unallocated space.


If you have many databases on an instance then you should appreciate a script that will gather this information for all those databases.


So here it is:

declare @stats table (
        name varchar (max),
        physicalName varchar (max),
        totalSizeMB int ,
        allocatedSizeMB int ,
        unallocatedSizeMB int

insert into @statsexec sp_msForEachDb 'use [?]; select ''?'' dbName,, df.physical_name, total_page_count * 8/1024 totalSizeMB, allocated_extent_page_count * 8/1024 allocatedSizeMB, unallocated_extent_page_count * 8/1024 unallocatedSizeMB from [sys].[dm_db_file_space_usage] su join [sys].[database_files] df on su.file_id = df.file_id'

select *
from @stats
order by unallocatedSizeMB

Sample output:


The last column - unallocatedSizeMB, represents an amount of disk space (in MB) that exists in the datafile and can be used for new rows or indexes without extending the file. In other words - even if there is no free disk space left on E and F drive, databases can still operate and an application can still insert new rows. SQL Server will reject INSERT request when there is no more unallocated space (unallocatedSizeMB=0) and none of the datafiles in the filegroup can extend. Then, there is a problem.

Do not miss valuable content. You will receive a monthly summary email. You can unsubscribe anytime.

We use cookies

We use cookies on our website. Some of them are essential for the operation of the site, while others help us to improve this site and the user experience (tracking cookies). You can decide for yourself whether you want to allow cookies or not. Please note that if you reject them, you may not be able to use all the functionalities of the site.