Geoff N. Hiten Blog

SQL Server thoughts, observations, and comments

Free Space Fundamentals

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/o:p

go/o:p

—– Create the following table or results come back empty/o:p

–Create Table  DBSpaceUsedFileGroupExclude (/o:p

–Databasename sysname,/o:p

–FilegroupName sysname/o:p

–)/o:p

 /o:p

————–/o:p

 /o:p

 /o:p

 /o:p

Drop procedure DBSpaceUsed /o:p

go/o:p

create procedure DBSpaceUsed as/o:p

set nocount on/o:p

Declare @DBName SysName/o:p

Declare @Cmd varchar(1000)/o:p

 /o:p

Create Table #FooTable (/o:p

      [DBName]    sysname NULL,/o:p

      [FileId]    int,/o:p

      [FileGroup] int,/o:p

      [TotalExtents]    int,/o:p

      [UsedExtents]     int,/o:p

      [DevName]   sysname,/o:p

      [FileName]  sysname,/o:p

      [FileGroupName]   Sysname NULL)/o:p

 /o:p

 /o:p

Declare DBCursor cursor READ_ONLY FORWARD_ONLY for Select [name] from master.dbo.sysdatabases/o:p

    where [name] in ('SiteData', 'JobAppl')/o:p

– Exclusion now based on table Admin.dbo.DBSpaceUsedFileGroupExclude.  Table contains database and filegroup/o:p

– names that are deleted from the final table./o:p

 /o:p

open DBCursor/o:p

Fetch next from DBCursor into @DBName/o:p

while 0 = @@Fetch_Status /o:p

      begin/o:p

      select @CMD = 'USE ' + @DBName + ' dbcc ShowFileStats with TABLERESULTS'/o:p

      insert INTO #FooTable (FileId,/o:p

      [FileGroup],/o:p

      TotalExtents,/o:p

      UsedExtents,/o:p

      DevName,/o:p

      [FileName]) exec (@cmd)/o:p

      update #FooTable set DBName = @DBName/o:p

      where DBName is null/o:p

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

            from #FooTable FT/o:p

            inner join sysfilegroups SFG/o:p

            on FT.[Filegroup] = SFG.[GroupID]/o:p

      where FT.[FileGroupName] IS NULL'/o:p

 /o:p

      exec (@Cmd)/o:p

      Fetch next from DBCursor into @DBName/o:p

      end/o:p

 /o:p

Close DBCursor/o:p

Deallocate DBCursor/o:p

delete from #FooTable /o:p

      from #FooTable FT/o:p

      inner join Admin.dbo.DBSpaceUsedFileGroupExclude EX/o:p

            on EX.Databasename = FT.DBName/o:p

            and EX.FileGroupName = FT.FileGroupName /o:p

 /o:p

select @@Servername as Server, DBName,         FileGroupName,/o:p

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

      (sum(TotalExtents)  64) /(1024 ) AS MBTotal,/o:p

      (sum(UsedExtents) 64) /(1024 ) AS MBUsed,/o:p

      (sum(TotalExtents - UsedExtents) 64) /(1024 ) AS MBFree/o:p

 /o:p

        from #FooTable FT/o:p

      group by DBName, FileGroupName/o:p

 /o:p

drop Table #FooTable/o:p

 /o:p

 /o:p

 /o:p