Post reply

Space Available

  • October 14, 2003 at 9:29 am

    #82442

    As you know when you show the properties of a database from within Enterprise Manager one of the values its shows is 'Space available'. Question is how do you get this value using SQL?

  • October 14, 2003 at 12:23 pm

    #478200

    well, there's sp_spaceused

  • October 15, 2003 at 1:48 am

    #478244

    quote:

    As you know when you show the properties of a database from within

    Enterprise Manager one of the values its shows is 'Space available'.

    Question is how do you get this value using SQL?

    EM uses an undocumented DBCC command

    DBCC SHOWFILESTATS

    If you execute this, you'll get something like

    

    Fileid      FileGroup   TotalExtents UsedExtents Name

    1           1           2454         1859        FAI_Daten

    

    (1 row(s) affected)

    Now given the size of the database, you'll have to calculate

    used_extents/total_extents * size_of_db = space used

    The difference between size_of_db - space used is space available

    The size of your db you'll get from sp_helpdb.

    Now, hopefully it is formatted here correctly. Have fun putting it all together

    HTH

    Frank

    http://www.insidesql.de

    Edited by - Frank Kalis on 10/15/2003 01:48:35 AM

    Edited by - Frank Kalis on 10/15/2003 01:49:20 AM

    Edited by - Frank Kalis on 10/15/2003 01:50:05 AM

    Edited by - Frank Kalis on 10/15/2003 01:51:03 AM

    Now, finally got the *!?~@ formatting to be acceptable

    Edited by - Frank Kalis on 10/15/2003 01:51:59 AM

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

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