How to know the available free space in every data file of a SQL 2005/2008 database

Below query will display the amount of free space available in every data file of a database.


SELECT name AS ‘File Name’, physical_name AS ‘Physical Name’, size/128 AS ‘Total Size in MB’,

(size/128.0)-(CAST(FILEPROPERTY(name,‘SpaceUsed’) AS int)/128.0) as ‘free space’  FROM sys.database_files

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)) , a.NAME, a.FILENAME from dbo.sysfiles a

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s