Geoff N. Hiten Blog

One of the easy ways to fail at database administration is to allow your databases to self-manage space.  Autogrow is a safety valve, not a pressure regulator.  Of course, to manage space you have to know exactly how much free space you have.  And since database objects are stored in filegroups, it would help to see free space by filegroups buth in MB and in percentages.  Well, here you go.

For those not familiar with my administration style, all my servers have an Admin database to store stuff like this. 

use Admin

go

----- Create the following table or results come back empty

--Create Table  DBSpaceUsedFileGroupExclude (

--Databasename sysname,

--FilegroupName sysname

--)

--------------

Drop procedure DBSpaceUsed

go

create procedure DBSpaceUsed as

set nocount on

Declare @DBName SysName

Declare @Cmd varchar(1000)

Create Table #FooTable (

      [DBName]    sysname NULL,

      [FileId]    int,

      [FileGroup] int,

      [TotalExtents]    int,

      [UsedExtents]     int,

      [DevName]   sysname,

      [FileName]  sysname,

      [FileGroupName]   Sysname NULL)

Declare DBCursor cursor READ_ONLY FORWARD_ONLY for Select [name] from master.dbo.sysdatabases

--    where [name] in ('SiteData', 'JobAppl')

-- Exclusion now based on table Admin.dbo.DBSpaceUsedFileGroupExclude.  Table contains database and filegroup

-- names that are deleted from the final table.

open DBCursor

Fetch next from DBCursor into @DBName

while 0 = @@Fetch_Status

      begin

      select @CMD = 'USE ' + @DBName + ' dbcc ShowFileStats with TABLERESULTS'

      insert INTO #FooTable (FileId,

      [FileGroup],

      TotalExtents,

      UsedExtents,

      DevName,

      [FileName]) exec (@cmd)

      update #FooTable set DBName = @DBName

      where DBName is null

      select @Cmd = 'USE [' + @DBName + '] Update #FooTable set [FileGroupName] = SFG.GroupName

            from #FooTable FT

            inner join sysfilegroups SFG

            on FT.[Filegroup] = SFG.[GroupID]

      where FT.[FileGroupName] IS NULL'

      exec (@Cmd)

      Fetch next from DBCursor into @DBName

      end

Close DBCursor

Deallocate DBCursor

delete from #FooTable

      from #FooTable FT

      inner join Admin.dbo.DBSpaceUsedFileGroupExclude EX

            on EX.Databasename = FT.DBName

            and EX.FileGroupName = FT.FileGroupName

select @@Servername as Server, DBName,         FileGroupName,

        convert(decimal (5,2), 100 - (sum(TotalExtents)-SUM(UsedExtents)) /(convert(numeric,sum(TotalExtents))) * 100) as PercentFull,

      (sum(TotalExtents)  * 64) /(1024 ) AS MBTotal,

      (sum(UsedExtents) * 64) /(1024 ) AS MBUsed,

      (sum(TotalExtents - UsedExtents) * 64) /(1024 ) AS MBFree

        from #FooTable FT

      group by DBName, FileGroupName

drop Table #FooTable