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

   

One Comment on “SQL Server – How to find the free space in Database files?”

  1. hello frnd,

    Is there any query to get the statistics of each databases in the sql server without scripting ???????

Leave a Reply

Your email address will not be published.