Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Posts Tagged ‘size’

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

Posted in Common | Tagged: , , | Leave a Comment »