Post reply

sys.master_files v.s. sp_spaceused

  • December 29, 2006 at 9:33 am

    #176225

    Can someone tell me why the results for size between the select statement for tempdb is different than the results from sp_spaceused?  The datafilesize + logfilesize from the select should equal the database_size from sp_spaceused.  I have a routine that logs space info about all DBs but it doesn't see to be working for tempdb.  The info I get back doesn't match sp_spaceused or the file size (which does match sp_spaceused).  I can post the full code later and see if there is any way to improve the process and share with others.  I have been changing it for SQL 2005.

    Thanks

     

    Select Master.sys.databases.database_id,

    Master

    .sys.databases.Name,

    Round(convert(dec(12,2),datafile.size) * 8 / 1024, 2) datafilesizeMB,

    Round(convert(dec(12,2),logfile.size) * 8 / 1024, 2) logfilesizeMB

    From

    Master.sys.databases

    Left

    Join Master.sys.master_files datafile on sys.databases.database_id = datafile.database_id and datafile.type in (0, 4) -- data file

    Left

    Join Master.sys.master_files logfile on sys.databases.database_id = logfile.database_id and logfile.type = 1 -- log file

     

    exec

    sp_spaceused

  • December 29, 2006 at 11:51 am

    #680572

    Don't you have to agregate across all types ?

    If your database happens to have more than one Data/log file your query does *not* work

    Also Tempdb has some other data structures (unique to it, like the version store) in 2005 not covered by sp_spaceused

    Cheers.


    * Noel

  • December 29, 2006 at 12:40 pm

    #680580

    Types 2 and 3 are reserved for future use but you are right it should agregate.

    You are correct that the query shown will not work for DBs that have the data or log split into more than one file each.

    I should have included that i my case the select statement returned only one row for tempdb with a total size(data+log) of 207.44 MB where sp_spaceused returned a total size of 4172.00 MB.  sp_spaceused returned the correct results.  I have confirmed that there are only two rows in sys.master_files for tempdb.

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

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