SQL Server – How to find the free space in Database files?

Hi All,

Todays Question of the Day: How to find the free space in Database files?

You must have faced a space crunch issue, when you actually wanted to know how much free space is available in a DataFile for a particular DB, This script will provide you all this info:

select
a.FILEID,
[FILE_SIZE_MB] = 
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a

The Script will provide you how much space is actually being used and how much is still available for it to grow further.

Regards

Sarabpreet Anand

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook