Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2004
    Posts
    54

    Unanswered: sp_spaceused database size

    How is it that the value returned by sp_spaceused is larger than the actual database size and unallocated space be negative? For example sp_spaceused retruns the following for one of our databases.

    database_size = 52022.31 MB
    unallocated space = -16462.47 MB

    reserved = 69559520 KB
    data = 68007688 KB
    index_size = 1463456 KB
    unused = 88376 KB

    That does not make much sense to me how the reserved size and even data size can be larger than the database_size.

    This database was 85 gigs yesterday but we were running extremely short on disk space so I was forced to do a shrink. I did update stats for the database as well.

    Thanks much.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you look at BOL?

    Remarks
    sp_spaceused computes the amount of disk space used for data and indexes, and the disk space used by a table in the current database. If objname is not given, sp_spaceused reports on the space used by the entire current database.

    When updateusage is specified, Microsoft® SQL Server™ scans the data pages in the database and makes any necessary corrections to the sysindexes table regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the sysindexes information for the table may not be current. This process can take some time to run on large tables or databases. Use it only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2004
    Posts
    54
    Brett,

    Thanks for your reply but I still don't understand how the amout of data reported by sp_spaceused can be larger than the actual database size. As I mentioned in my first post I did update the database stats so it should all be current.

    If I updated the stats and sp_spaceused reports that the database has reserved 69559520 KB, shouldn't the database_size be at least 69559520 KB?

    Maybe I am missing something here.

    Thanks much.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you read the part about updateusage?

    Or the part about the indexes?

    If you want to dig "uder the covers"

    execute this

    USE master
    GO

    sp_helptext sp_spaceused
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2004
    Posts
    54
    Brett,

    I read both parts and neither of them really told me what I am looking for. I want to know how there can be more data in the database than is actually stored on disk. According to sp_spaceused the amount of data is 68007688 KB or approx 66413.76 MB, but the allocated size on disk is only 52022.31 MB. Is the data value returned by sp_spaceused not accurate? It should be because I updated the db stats. What am I missing here?

    Thanks again.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This statement

    When updateusage is specified, Microsoft® SQL Server™ scans the data pages in the database and makes any necessary corrections to the sysindexes table regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the sysindexes information for the table may not be current.
    Leads me to believbe that it can get out of whack and may not be accurate.

    Don't you think?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2004
    Posts
    54
    Brett,

    Thanks for clarifying. I took that as updating the statistics wouuld correct all the values in the sysindexes table. Meaning that after update stats was ran, everything should be correct and current.

    If what you say is true, do you know how I could update all the current values so I can get accurate results from sp_spaceused?

    Thanks much.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    USE pubs
    sp_spaceused @updateusage = 'TRUE'


    Or, because your db is so massive

    This process can take some time to run on large tables or databases. Use it only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Aug 2004
    Posts
    54
    Brett,

    Sorry, I was under the impression updating the stats of the database also would update the usage. I will have to try and run this tonight when it is not being used.

    Thanks for all of your help.

  10. #10
    Join Date
    Aug 2004
    Posts
    54
    Brett,

    I was able to run dbcc updateusage, and everything was correct after that.

    Thanks again for all your help and sorry for the confusion.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •