Post reply

sp_spaceused and DBCC SQLPERF ( LOGSPACE ) query

  • September 8, 2009 at 2:57 am

    #141354

    Hi all

    I have used sp_spaceused to look at how much space my datafile is using. The Reserved space for this is 1.769GB.

    I then used DBCC SQLPERF ( LOGSPACE ) to look at my logspace which is 2.527GB

    This totals to 4.296GB

    If however I look at the properties of my database in SSMS this states the database size to be 4.330GB so I am missing 34MB.

    I know this is probably something to do with the way SQL manages datafile sizes but was wondering if anyone could clear up exactly where this space could be as I have done many additions and subtractions using sp_spaceused and DBCC SQLPERF ( LOGSPACE ) but I can't see where this would be.

    Thanks for any help you can provide

  • September 8, 2009 at 3:02 am

    #1050354

    Did u use DBCC UPDATEUSAGE before running sp_spaceused or atleast used the @updateusage option?

  • September 8, 2009 at 3:06 am

    #1050357

    What figure does sp_spaceused show as database_size in the first result set?

    The second result set only shows what space has been allocated to objects in the database (data, indexes). It doesn't show what size has been allocate to the files.

    The 'missing' 34MB is 'space' in the files.

    If you post all the figures you have we can show you the calculation that will account for all the figures

    Kev

  • September 8, 2009 at 3:34 am

    #1050365

    Ok thanks for this, as you requested the complete set of stats are here.

    Database NameLog Size (MB)Log Space Used (%)Status

    xxxx 2527.492 7.69184 0

    database_namedatabase_sizeunallocated space

    xxxx 4337.56 MB 83.33 MB

    reserved data index_sizeunused

    1768176 KB 1313120 KB 363376 KB 91680 KB

    I did not UPDATEUSAGE and according to BOL this only needs to be run after an upgrade to 2005 which this was not.

    Sorry if the formatting is a little out it was right at time of writing.

    Thanks

  • September 8, 2009 at 3:52 am

    #1050374

    from the 2nd result set of sp_spaceused, you get

    1313120KB data + 363376KB index + 91680KB allocated but as yet unused = 1768176KB reserved (as stated in first column) = 1768176 / 1024 = 1727MB

    from dbcc sqlperf(logspace) you get

    2527MB log

    1727MB data + 2527MB log = 4254MB used + 83MB unused (from 1st result set of sp_spaceused)

    = 4337MB

    matches the 1st column from the 1st result set of sp_spaceused, and should match what you see in SSMS.

  • September 8, 2009 at 7:23 am

    #1050493

    🙁 It now adds up to more than what SSMS is displaying, its over by about 30MB now :s

    I have however tried this calculation on other dbs and it works.

Viewing 6 posts - 1 through 5 (of 5 total)

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