none
T-SQL for database size and space available (like on Database Properties page on SQL 2008 SSMS)

    Question

  • How can i (using T-SQL) get the database size and free space like the database properties page of SQL 2008 SSMS shows? I want to be able to display that information in our software.

    thanks.

    • Changed type darinhorton Thursday, October 21, 2010 6:05 PM
    Thursday, October 21, 2010 5:46 PM

Answers

  • Take a look at the 

    How to get information about all databases without a loop

    From that blog:

    DECLARE @SQL VARCHAR(MAX)
    SELECT @SQL =COALESCE(@SQL + CHAR(13) + 'UNION ALL
    ' ,'') + 'SELECT ''' + name + ''' AS DBNAME,' +
    'sum(size * 8 /1024.0) AS MB from ' + QUOTENAME(name) + '.dbo.sysfiles'
    FROM sys.databases
    ORDER BY name
     
    EXECUTE (@SQL)
    
    And in your case you may want to just run the query from sysFiles for your name of the database.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by darinhorton Thursday, October 21, 2010 9:04 PM
    Thursday, October 21, 2010 6:08 PM
    Moderator
  • Check sp_SpaceUsed BOL Reference
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by darinhorton Thursday, October 21, 2010 9:04 PM
    Thursday, October 21, 2010 7:08 PM
    Moderator
  • Apparently not. See this thread MSDN Thread about sp_SpaceUsed You may vote in the Connect Item
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by darinhorton Thursday, October 21, 2010 9:04 PM
    Thursday, October 21, 2010 8:06 PM
    Moderator

All replies

  • Take a look at the 

    How to get information about all databases without a loop

    From that blog:

    DECLARE @SQL VARCHAR(MAX)
    SELECT @SQL =COALESCE(@SQL + CHAR(13) + 'UNION ALL
    ' ,'') + 'SELECT ''' + name + ''' AS DBNAME,' +
    'sum(size * 8 /1024.0) AS MB from ' + QUOTENAME(name) + '.dbo.sysfiles'
    FROM sys.databases
    ORDER BY name
     
    EXECUTE (@SQL)
    
    And in your case you may want to just run the query from sysFiles for your name of the database.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by darinhorton Thursday, October 21, 2010 9:04 PM
    Thursday, October 21, 2010 6:08 PM
    Moderator
  • That gives me the size of the database, what about the free space?
    Thursday, October 21, 2010 6:54 PM
  • Check sp_SpaceUsed BOL Reference
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by darinhorton Thursday, October 21, 2010 9:04 PM
    Thursday, October 21, 2010 7:08 PM
    Moderator
  • got all of that - thanks. that returns two "tables" with three columns and 4 columns.

    Is there a T-SQL command i can run that only returns the "unallocated space" (the third column from the first group)?

    having that means my program just does one command and that is all that is returned. Maybe something in a function?

    Thursday, October 21, 2010 7:54 PM
  • Apparently not. See this thread MSDN Thread about sp_SpaceUsed You may vote in the Connect Item
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by darinhorton Thursday, October 21, 2010 9:04 PM
    Thursday, October 21, 2010 8:06 PM
    Moderator
  • For everyone interested, i created a function that all it does is return the free space of the current database:

     

    CREATE FUNCTION dbo.fUTFreeSpace
    (
    )
    RETURNS int
    begin
    DECLARE @dbsize bigint ,@reservedpages  bigint 
    DECLARE @xout int
     
    select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) 
    from dbo.sysfiles 
     
    select @reservedpages = sum(a.total_pages)
    from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id 
    left join sys.internal_tables it on p.object_id = it.object_id 
     
    select @xout=case when @dbsize >= @reservedpages then convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages) else 0 end
    return @xout*8/1024
    end

    Thursday, October 21, 2010 9:34 PM
  • Run this on the DB in question:

    SELECT

    DBInfo

    .*,

     

    partitionData

    .*

     

    --TotalSpaceText
     

     

    ,ltrim(str((convert (dec (15,2),dbsize) + convert (dec (15,2),logsize)) * 8192 / 1048576,15,2) + ' MB') as TotalSpaceText

     

     

    --FreeSpaceText
     

     

    ,ltrim(str((

     

    case

     

    when dbsize >= reservedpages

     

    then (convert (dec (15,2),dbsize) - convert (dec (15,2),reservedpages)) * 8192 / 1048576

     

    else

    0

     

    end),15,2) + ' MB'

     

    ) as FreeSpaceText

     

    --TotalSpace

     

    ,(convert(dec(15,2),dbsize)+convert(dec(15,2),logsize)) * 8192 / 1048576 as TotalSpace

     

    --FreeSpace

     

    ,( case

     

    when dbsize >= reservedpages

     

    then (convert (dec (15,2),dbsize) - convert (dec (15,2),reservedpages)) * 8192 / 1048576

     

    else

    0

     

    end

     

    ) as FreeSpace

     

    --UsedSpace (TotalSpace - FreeSpace)

     

    ,((convert(dec(15,2),dbsize)+convert(dec(15,2),logsize)) * 8192 / 1048576) - (case

     

    when dbsize >= reservedpages

     

    then (convert (dec (15,2),dbsize) - convert (dec (15,2),reservedpages)) * 8192 / 1048576

     

    else

    0

     

    end

     

    ) as UsedSpace

    FROM

     

    (

     

    select

     

    sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) as dbsize

     

    ,sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) as logsize

     

    from

    dbo

    .sysfiles

     

    ) as DBInfo

     

    INNER JOIN

     

    (

     

    select

     

    sum(a.total_pages) as reservedpages,

     

    sum(a.used_pages) as usedpages,

     

    sum(

     

    CASE

     

    -- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"

     

    When it.internal_type IN (202,204) Then 0

     

    When a.type <> 1 Then a.used_pages

     

    When p.index_id < 2 Then a.data_pages

     

    Else 0

     

    END

     

    ) as pages

     

     

    from

     

    sys.partitions p

     

    join

     

    sys.allocation_units a

     

    on p.partition_id = a.container_id

     

    left join

     

    sys.internal_tables it

     

    on p.object_id = it.object_id

     

    ) as partitionData

     

    on 1=1

     

    Monday, May 30, 2011 2:29 AM