Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Available File group size

Posted by Simon Cho on 08/04/2011

SELECT ISNULL(b.groupname, 'LOG') AS 'File Group'
, Name
, [Filename]
, CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space(MB)]
, CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)]
, CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]
, CONVERT(Decimal(15,2),CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))/CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2))*100) FreeSpace_Ratio
FROM dbo.sysfiles a (NOLOCK)
left outer JOIN sysfilegroups b (NOLOCK)
ON a.groupid = b.groupid
--ORDER BY b.groupname

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s