Post reply

Physical Data files smaller than the database_size

  • October 19, 2009 at 8:08 am

    #136617

    Hello everyone,

    I need your advice about this issue,

    I run sp_spaceused @updateusage= 'true'

    for the database, and I obtained the result as following,

    database_size = 78355.69 MB,

    unallocated_size =2653.86MB,

    Reserverd Size=72192848 KB,

    Data_Size= 55941968 KB,

    Index_Size=14775552KB,

    Unused_Size=1475328KB

    I am curious that my physical .mdf file just consists of 74 GB, and why my database size is larger than the existing physical mdf file, kindly advice.

    Can anybody explain in advance about the result set above?

    Thanks in advance.

  • October 19, 2009 at 8:37 am

    #1067674

    I would run

    use databasename

    go

    dbcc updateusage(0)

    update_usage updates the information used by sp_spaceused.

    so if you see differences that you cannot explain, try running the above

    from BOL

    Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

    Although you are specifying the updateusage parameter with the sp_spaceused call 😉

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • October 19, 2009 at 8:49 am

    #1067684

    Don't forget that the size that you get from sp_spaceused included the size of all files including the log files. Check the physical size of all database's files and see if you get the correct number.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • October 19, 2009 at 9:03 am

    #1067702

    Hi Adi,

    Thanks for the information.

    I thought it clear my doubts.

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

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