Check SQL Server Database Free Space

---

Disclaimer

Click the images in the article below to toggle between thumbnail and full-size. Enlarge all | Reduce all

---

Introduction

This article shows how to view space utilization statistics for the SQL Server databases on your system. Using a command line script you can see how much space has been allocated and how much is free.

You might run this because you‘re concerned that you‘re nearly out of free space, or on the other hand, you might suspect that a database has more free space than it really needs.

Solution

Here's a script you can run in a Management Studio query window. It will give you free space statistics for all of the databases on your system.

Create Table ##temp
(
    DatabaseName sysname,
    FileNameAndPath nvarchar(500),
    Allocated_Size_MB decimal (18,2),
    FreeSpace_MB decimal (18,2)
)   
Exec sp_msforeachdb '
Use [?];
Insert Into ##temp (DatabaseName, FileNameAndPath, Allocated_Size_MB, FreeSpace_MB)
    Select DB_NAME() As [DatabaseName], physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) 
        As Allocated_Size_MB,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) 
        As FreeSpace_MB
    From sys.database_files
'
Select * From ##temp
drop table ##temp
                	

Final Remarks

It looks like the HypEPMRepository database is larger than necessary. Its .mdf file has an allocation of 500MB but it's only using a little over 1MB, and its .ldf file has an allocation of 62.2MB but it's using less than 1MB. The decision to reduce the file sizes for a database is complex. If you have weighed the considerations and decided you want to reduce the file sizes, here's an article that shows how: Reduce SQL Server Database Size

-Tom Cockerline, February 2017