Post reply

data file increase after index rebuild

  • November 25, 2013 at 8:46 am

    #100541

    I have SQL Server 2000 database, ran index rebuild job over the weekend, but the data file increase after the index rebuild. How can I regain the space? Thank you!

  • November 25, 2013 at 8:51 am

    #1668840

    Grace09 (11/25/2013)

    I have SQL Server 2000 database, ran index rebuild job over the weekend, but the data file increase after the index rebuild. How can I regain the space? Thank you!

    i'd expect to see the log file increase in size due to the rebuild.

    if the data went up in size, to me that kind of implies that someone used a fill factor on the index rebuilds that had not been there before, does that sound posible?

    if you run exec sp_spaceused in the database in question, what are your specific results?

    for example, here's the results of mine:

    database_name database_size unallocated space

    --------------- ------------------ ------------------

    MyDatabaseName 44540.50 MB 7463.08 MB

    reserved data index_size unused

    ------------------ ------------------ ------------------ ------------------

    33536048 KB 15968088 KB 17526760 KB 41200 KB

    so, based on unallocated space, i could try to get back 7.4 gig or so, but in my case, i don't want to do that, that space , for me, is fine for future growth, and i'd leave it alone.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 25, 2013 at 9:35 am

    #1668874

    Normal, fully expected. SQL needs space to put the new index. A rebuild creates the new index then drops the old. So if there wasn't space in the data file for the new index to be put down, the file would have to grow.

    As for reclaiming, why? The file's just going to grow again if you do, and shrinking will fragment all your indexes, wasting all the time spent rebuilding them. Free space in a data file is a good thing.

    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

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

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