Free Space In Data Files

Quick way to find free space in SQL Server data files.

This helps find skewed and lumpy data distribution.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82359

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

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