[thelist] [TIP] Finding File Space Info for MS SQL Databases....

[Anthony Baratta]

<tip author="Anthony Baratta" type="MS SQL DB File Space Stored Procedure">
I found a nifty stored procedure that I've modified for my own purposes, but I wanted to post the link and the modified code for wider distribution. There is probably many ways to tighten and speed up this script, but I was in a hurry and it gets the job done for now.

The original source for the SP lives here:

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=84895&enterthread=y

I tweaked is so that the data returned was a bit more useful for my needs, and also added a parameter that switches the output to XML.  I now  have a quick and dirty dotNet Application that uses a drop down to select the SQL Instance, and then builds the connection string on the fly to the target SQL Instance, runs this SP, and formats the XML to a nice HTML layout for viewing. This gives me a quick window into the database file and log sizes with one screen. No more Enterprise Manager drill downs, and having to pull up file explorer on the target machine to see DB File Sizes.

Anyway - use as you see fit.


USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetDBFileSpaceInfo]    Script Date: 04/23/2007 09:56:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_GetDBFileSpaceInfo]
    @Format as varchar(5) = null
AS
BEGIN

    SET NOCOUNT ON

    Create Table #t 
    (
        dbid int,
        name varchar(64),
        datafile varchar(64),
        dfileLogical varchar(64),
        datapages int,
        datapagesUsed int,
        logfile varchar(64),
        lfileLogical varchar(64),
        logpages int,
        logpagesUsed int
    )

    declare @sql nvarchar(500)
    Declare @param nvarchar(500)
    declare @i int
    declare @iOUT int
    declare @j int
    declare @jOUT int
    Declare @dfile varchar(255)
    declare @lFile varchar(255)

    Declare c Cursor
        Read_Only
        For
        SELECT dbid, name
        FROM sysdatabases

    Declare @dbid int
    Declare @name varchar(60)

    Open c
        Fetch Next From c into @dbid, @name
            While (@@fetch_status <> -1)
            Begin
                IF (@@fetch_status <> -2)
                Begin
                    exec('
                        INSERT #t (dbid, name, datafile, datapages, dfilelogical)
                        SELECT ' + @dbid + ', ''' + @name + ''',
                        Reverse(left(ltrim(reverse(filename)), charindex(''\'', ltrim(reverse(filename)))-1)),
                        size, name
                        from [' + @name + ']..sysfiles
                        WHERE fileid = 1
                    ')
                        
                    exec ('
                        UPDATE #t
                        Set logfile = (select Reverse(left(ltrim(reverse(filename)), charindex(''\'', ltrim(reverse(filename)))-1)) from [' + @name + ']..sysfiles where fileid = 2),
                        logpages = (select size from [' + @name + ']..sysfiles where fileid = 2),
                        lfilelogical = (select name from [' + @name + ']..sysfiles where fileid = 2)
                        where dbid = ' + @dbid
                    )
                    
                    select @dfile = dfileLogical, @lfile = lFileLogical from #t where dbid = @dbid
                    
                    set @sql = N'use ' + @name + char(13) + 'select @iOUT= fileproperty(''' + @dfile + ''', ''spaceused''), @jOUT = fileproperty(''' + @lfile + ''', ''spaceused'')'
                    set @param = N'@iOUT int out, @jOUT int out'
                    
                    exec sp_executesql @sql, @param, @iOUT = @i out, @jOUT = @j out
                    
                    update #t
                        set datapagesused = @i,
                        logpagesused = @j
                        where dbid = @dbid
                    
                End
                Fetch Next From c into @dbid, @name
            End
        
        close c
        deallocate c

    if @Format = 'XML'
        select 
              dbid
            , name dbName
            , datafile
            , datapages
            , datapagesused 
            , CAST((CAST(datapages as decimal(20,3)) * 8)/1024 as decimal(20,3)) dataTotalMb
            , CAST((CAST(datapagesused as decimal(20,3)) * 8)/1024 as decimal(20,3)) dataUsedMb
            , CAST((CAST((datapages - datapagesused) as decimal(20,3)) * 8)/1024 as decimal(20,3)) dataFreeMb
            , logfile
            , logpages
            , logpagesused 
            , CAST((CAST(logpages as decimal(20,3)) * 8) / 1024 as decimal(20,3)) logTotalMb
            , CAST((CAST(logpagesused as decimal(20,3)) * 8)/1024 as decimal(20,3)) logUsedMb
            , CAST((CAST((logpages - logpagesused) as decimal(20,3)) * 8)/1024 as decimal(20,3)) logFreeMb
        from #t as DBFileSpaceInfo 
        order by [name] 
        FOR XML AUTO, ELEMENTS
    else
        select 
              dbid
            , name dbName
            , datafile
            , datapages
            , datapagesused 
            , CAST((CAST(datapages as decimal(20,3)) * 8)/1024 as decimal(20,3)) dataTotalMb
            , CAST((CAST(datapagesused as decimal(20,3)) * 8)/1024 as decimal(20,3)) dataUsedMb
            , CAST((CAST((datapages - datapagesused) as decimal(20,3)) * 8)/1024 as decimal(20,3)) dataFreeMb
            , logfile
            , logpages
            , logpagesused 
            , CAST((CAST(logpages as decimal(20,3)) * 8) / 1024 as decimal(20,3)) logTotalMb
            , CAST((CAST(logpagesused as decimal(20,3)) * 8)/1024 as decimal(20,3)) logUsedMb
            , CAST((CAST((logpages - logpagesused) as decimal(20,3)) * 8)/1024 as decimal(20,3)) logFreeMb
        from #t as DBFileSpaceInfo 
        order by [name] 

    drop table #t

END


</tip>