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
SELECT      
      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

Find available space in a file group on SQL Server

Yesterday I attempted to add a new index to my development database and it returned an error stating that the file group didn't have enough disk space.  After a bit of searching I discovered the following code that would show the Total allocated and free MB worth of disk space in each file group.

-- check to make sure temp tables don't exist
IF (OBJECT_ID('tempdb..#fileStats') IS NOT NULL)
            drop table #fileStats

IF (OBJECT_ID('tempdb..#fileGroup') IS NOT NULL)
            drop table #fileGroup


-- Create temporary holding tables
create table #fileStats
    (
        fileID          int,
        fileGroup       int,
        totalExtents    int,
        usedExtents     int,
        name            varchar(255),
        fileName        varchar(1000)
    )

create table #fileGroup
    (
        groupid         int,
        groupname       varchar(256)
    )


-- Insert 
insert into #fileStats
    exec ('DBCC showfilestats with no_infomsgs')

-- Insert
insert into #fileGroup
    select  groupid, groupname
    from sysfilegroups

-- Get resulting set
-- size returned from showfilestats is in Extents -> 1 Extent is 64kb
-- so we need to multiply the sum of TotalExtents by 64 and then divide by 1024 to get
-- the actual MB

select  g.groupname,
        sum(TotalExtents)*64.0/1024                         AS [Total MB],
        sum((TotalExtents - UsedExtents) * 64.0 / 1024.0)   AS [Free MB]

    from #filestats     AS f
        join #filegroup AS g
            on f.filegroup = g.groupid

group by g.groupname


-- Clean up after self
drop table #filestats
drop table #filegroup

set nocount off