Script to find amount of free space in datafiles

Datafiles 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.name, 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.