Post reply

After reindexing, size of database has grown

  • August 22, 2008 at 11:39 pm

    #72721

    Friends,

    After reindexing my SQL 2005 Database, its size has grown from 45 GB to 57 GB.

    Why so ? Is it normal or I need to carry out any more procedure ?

    Thanks

  • August 23, 2008 at 4:31 am

    #862211

    Perfectly normal. Rebuilding indexes means putting all the leaf pages down in order. next to one another. If there isn't space available to do that, SQL will frow the file to make space.

    Nothing you need to do.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 23, 2008 at 6:30 am

    #862223

    If you specified "alter index.. REORGANIZE" or "DBCC INDEXDEFRAG", then you have a very strange situation.

    But, if you actually specified "alter index.. REBUILD" or "DBCC DBREINDEX", which copies the data and indices to new space and, when the copy completes, releases the old space for reuse, then the file growth is expected.

    There are more details in a technet Q&A at http://technet.microsoft.com/en-us/magazine/cc162466.aspx

    One word of caution, if you decide to shrink the database file, this will cause the indexes to fragment and you will then need to run the REBUILD again.

    Best practice is that a database file's free space of should be at least 1.2 times the size of the largest table including the indexes.

    SQL = Scarcely Qualifies as a Language

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

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