Wednesday, December 26, 2012

Find available space in a file group on SQL Server part 2

Well, it seems the DBA's frowned upon my access rights and quietly killed them.  My previous post regarding finding the available space on the server no longer works.  This new query though does, though...

-- check to make sure temp tables don't exist
      b.groupname AS 'File Group',
      SUM(CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2))) 

          AS [Currently Allocated Space (MB)],
      SUM(CONVERT (Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2))) 

          AS [Space Used (MB)],
      SUM(CONVERT (Decimal(15,2), ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))) 

          AS [Available Space (MB)]
     FROM dbo.sysfiles       AS a WITH (NOLOCK)
          JOIN sysfilegroups AS b WITH (NOLOCK) 

             ON a.groupid = b.groupid
     GROUP BY B.groupname