Finding Space Used,Space left on Data and Log files

Posted: December 26, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , , ,

Here is a script from which we can easily find the Spaceused on MDF and LDF files.

Select DB_NAME() as [DATABASE NAME],
       fileid 
as FILEID,
       CASE WHEN groupid = 0 then ‘LOG FILE’ else ‘DATA FILE’ END as FILE_TYPE,
       Name as PHYSICAL_NAME,
       Filename as PHYSICAL_PATH,
       Convert(int,round((sysfiles.size*1.000)/128.000,0)) 
as FILE_SIZE,
       Convert(int,round(fileproperty(sysfiles.name,‘SpaceUsed’)/128.000,0)) as SPACE_USED,
       Convert(int,round((sysfiles.sizefileproperty(sysfiles.name,‘SpaceUsed’))/128.000,0)) as SPACE_LEFT
From 
sysfiles;

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