Database Size Increase After Index Rebuild

Question

Database Size Increase After Index Rebuild

rob 58081

SSC Veteran

Points: 209
More actions
May 16, 2010 at 4:23 pm

#221107

Hi
I recently perfomed an Index Rebuild using a Maintenance Plan on a large table in my database because it was 91% fragmented. After the Index Rebuild the database file increased in size from 18605 MB to 32364 MB.
The Index Rebuild was set to 'Reorganise pages with the default amount of free space'.
Is there anyway to stop my database increasing in size as a result of my Index maintenance?

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

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

rob 58081 (5/16/2010)
Hi
I recently perfomed an Index Rebuild using a Maintenance Plan on a large table in my database because it was 91% fragmented. After the Index Rebuild the database file increased in size from 18605 MB to 32364 MB.
The Index Rebuild was set to 'Reorganise pages with the default amount of free space'.
Is there anyway to stop my database increasing in size as a result of my Index maintenance?

I'm not 100% sure but, if you have the Enterprise Edition, you could use the "Sort In TempDB" option and I believe it will keep that expansion from happening. Of course, it's going to make TempDB grow.

The ironic part is that if you shrink your DB after it grows, then you frag the very tables and indexes you just fixed with the Index Rebuild. I wish MS would fix the "shrink" methods so they actually worked correctly. You know, more like Norton's Speed Disk or Disk Keeper. As it currently is, shrinking the database is one of the worst things you can do unless you stick to the "Release free space" option (IIRC).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Pei Zhu-415513 SSCarpal Tunnel Points: 4040 More actions · Answer 2

You roughly need additional 1.2 time space of the biggest index. the .2 can go tempdb if you use SORT_IN_TEMPDB.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

rob 58081 (5/16/2010)
I recently perfomed an Index Rebuild using a Maintenance Plan on a large table in my database because it was 91% fragmented. After the Index Rebuild the database file increased in size from 18605 MB to 32364 MB.

I'm in agreement with previous posts.

Let me add that databases are very peculiar entities, they always manage to go back at the size they feel comfortable no matter what you do. By the way - shrinking a database gives you a temporary sense of fullfilment but you pay for it on processing overhead and fragmentation.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

I'm not so sure there's a penalty if you use just the "Release Unused Space" in the "File" shrink. I don't believe there's any reorganization when that particular option is used.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)