Quickly Viewing Available Space

  • Bikeman

    Ten Centuries

    Points: 1207

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bBaliner/quicklyviewingavailablespace.asp

  • Leendert van Staalduinen

    Right there with Babe

    Points: 767

    OK, this is valuable information.

    But did I miss something after "... just run the following code:" ?

    Leendert.

  • Stephen Anderson-260031

    SSC Veteran

    Points: 237

    Try this and compare it to the task pad view in Enterprise Manager.

     

     SELECT AA.name, AA.filename, -- AA.dbSize,

      BB.name, BB.filename, --BB.dbSize,

      (AA.dbSize) * CC.low / 1048576 AS DBSize, -- This is the total database size.

      (AA.dbSize) * CC.low / 1048576 - AA.AvailableSpaceInMB AS DBUsedSpace,

      AA.AvailableSpaceInMB as DBAvailableSpaceInMB,

      (BB.dbSize) * CC.low / 1048576 AS LogSize,

      (BB.dbSize) * CC.low / 1048576 - BB.AvailableSpaceInMB AS LogUsedSpace,

      BB.AvailableSpaceInMB as LogAvailableSpaceInMB,

      (AA.dbSize + BB.dbSize) * CC.low / 1048576 AS TotalSize,

      AA.AvailableSpaceInMB + BB.AvailableSpaceInMB as TotalAvailableSpaceInMB

     FROM

     (select name, filename, convert(dec(15),size) as dbSize,

      size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB

      from dbo.sysfiles

      where (status & 64 = 0)) AA CROSS JOIN

     (select name, filename, convert(dec(15),size) as dbSize,

      size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB

      from dbo.sysfiles

      where (status & 64 <> 0)) BB CROSS JOIN

     (select low

      from master.dbo.spt_values

      where number = 1

       and type = 'E') CC

     

  • Don Schaeffer

    SSC Veteran

    Points: 279

    This is nice, but what if I want to see available space for all databases on my server?  I tried doing this by using the sp_msforeachdb proc, but for some reason I'm getting nulls for AvailableSpaceInMB.  Anybody know why?

    exec sp_msforeachdb 'SELECT ''?'' as DBName, name AS FileName,

      size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )AS int)/128.0 AS AvailableSpaceInMB

     FROM [?].dbo.SYSFILES'

    I could always result to using a cursor, looping thru the databases but I'd rather not do that.  If I can get the above statement to work, the next step would be to insert the results into a temp table so I can then see the free space for all files for all databases as the result of a single query.

     

  • Stephen Anderson-260031

    SSC Veteran

    Points: 237

    The fileproperty function needs to be used in the context of the database.  I plan to call this from an MS Access database (I will convert it to .Net later) looping through each database on the server.  I will also be using this on multiple servers.

    My plan is to use this to determine file growth, space issues, and to help project future disk space needs.

  • Wayne Fairless

    SSC Veteran

    Points: 220

    -- Try this.  It'll go through all the DBs if no name is specified, otherwise, just -- return it for the DB specified.

    -- Wayne Fairless, DBA

    USE

    MASTER

    GO

    IF

    (SELECT COUNT(*) FROM dbo.sysobjects WHERE NAME = N'proc_ShowAvailableSpace') = 1

    BEGIN

         DROP PROCEDURE dbo.proc_ShowAvailableSpace

    END

    GO

    CREATE

    PROCEDURE dbo.proc_ShowAvailableSpace

    @DBName NVARCHAR(256) = NULL

    AS

    SET

    NOCOUNT ON

    DECLARE

    @TSQL NVARCHAR(512),

    @RETCODE

    INT,

    @CurrentDB

    VARCHAR(256)

     

    IF

    @DBName IS NULL

    BEGIN

    DECLARE DB_CUR CURSOR

    FOR

    SELECT NAME FROM master.dbo.sysdatabases

    ORDER BY NAME

    OPEN DB_CUR

    FETCH NEXT FROM DB_CUR INTO @CurrentDB

    PRINT @CURRENTDB

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @TSQL = 'SELECT name AS [File Name (' + @CurrentDB + ')], ' +

    'size/128.0 -CAST(FILEPROPERTY(name, ' +

    CHAR(39) + 'SpaceUsed' + CHAR(39) +

    ')AS int)/128.0 AS [Available Space (MB)] ' +

    'FROM [' + @CurrentDB + '].[dbo].[SYSFILES]'

    EXEC (@TSQL)

    FETCH NEXT FROM DB_CUR INTO @CurrentDB

    END

    CLOSE DB_CUR

    DEALLOCATE DB_CUR

    END

    ELSE

    BEGIN

    PRINT 'Free space for database "' + @DBName + '":'

    -- Check to see if the DB exists

    IF (SELECT DB_ID(@DBName)) IS NULL

    BEGIN

    PRINT @DBName + 'does not exist.'

    RETURN

    END

    ELSE

    BEGIN

    SET @TSQL = 'SELECT name AS [File Name], ' +

    'size/128.0 -CAST(FILEPROPERTY(name, ' +

    CHAR(39) + 'SpaceUsed' + CHAR(39) +

    ')AS int)/128.0 AS [Available Space (MB)] ' +

    'FROM [' + @DBName + '].[dbo].[SYSFILES]'

    EXEC (@TSQL)

    END

    END

     

    -- EXEC dbo.proc_ShowAvailableSpace 'master'

    -- EXEC dbo.proc_ShowAvailableSpace

     

  • J.T. Shyman

    SSCrazy

    Points: 2582

    Returns all Nulls for the space values when running connected to a SQL 2000 server

    -- J.T.

    "I may not always know what I'm talking about, and you may not either."

  • Wayne Fairless

    SSC Veteran

    Points: 220

    Opps.  Forgot to change the DB between cursor lookups.  Edited to work on case sensitive collations, too.  Try this one instead....

    -- Wayne Fairless, DBA

    USE

    MASTER

    GO

    IF

    (SELECT COUNT(*) FROM dbo.sysobjects WHERE NAME = N'proc_ShowAvailableSpace') = 1

    BEGIN

    DROP PROCEDURE dbo.proc_ShowAvailableSpace

    END

    GO

    CREATE

    PROCEDURE dbo.proc_ShowAvailableSpace

    @DBName

    NVARCHAR(256) = NULL

    AS

    SET

    NOCOUNT ON

    DECLARE

    @TSQL NVARCHAR(512),

    @RETCODE

    INT,

    @CurrentDB VARCHAR

    (256)

     

    IF

    @DBName IS NULL

    BEGIN

    DECLARE DB_CUR CURSOR

    FOR

    SELECT NAME FROM master.dbo.sysdatabases

    ORDER BY NAME

    OPEN DB_CUR

    FETCH NEXT FROM DB_CUR INTO @CurrentDB

    PRINT @CURRENTDB

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @TSQL = 'USE ' + @CurrentDB+ '; SELECT name AS [File Name (' + @CurrentDB + ')], ' +

    'size/128.0 -CAST(FILEPROPERTY(name, ' +

    CHAR(39) + 'SpaceUsed' + CHAR(39) +

    ')AS int)/128.0 AS [Available Space (MB)] ' +

    'FROM [' + @CurrentDB + '].[dbo].[sysfiles]'

    EXEC (@TSQL)

    --print @tsql

    FETCH NEXT FROM DB_CUR INTO @CurrentDB

    END

    CLOSE DB_CUR

    DEALLOCATE DB_CUR

    END

    ELSE

    BEGIN

    PRINT 'Free space for database "' + @DBName + '":'

    -- Check to see if the DB exists

    IF (SELECT DB_ID(@DBName)) IS NULL

    BEGIN

    PRINT @DBName + 'does not exist.'

    RETURN

    END

    ELSE

    BEGIN

    SET @TSQL = 'SELECT name AS [File Name], ' +

    'size/128.0 -CAST(FILEPROPERTY(name, ' +

    CHAR(39) + 'SpaceUsed' + CHAR(39) +

    ')AS int)/128.0 AS [Available Space (MB)] ' +

    'FROM [' + @DBName + '].[dbo].[sysfiles]'

    EXEC (@TSQL)

    END

    END

    -- EXEC dbo.proc_ShowAvailableSpace 'master'

    -- EXEC dbo.proc_ShowAvailableSpace

  • dougznospam-mailbox

    Old Hand

    Points: 314

    My favorite way to view available space in SQL is with a simple HTML Application I wrote. You can download it here: http://www.dougzuck.com/hta

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply