Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to create a weekly database plan for my company's databases (5.8Gb), now I want to rebuild the Indexes, and then want to trigger a Shrink operation. Now I know Shrink creates Index defragmentation, so can anyone guide me how to create this maintenance plan... Your suggestion will be very much valuable to me...Please

share|improve this question
1  
But the data shrink is going to ruin all your indexes that you've just rebuilt - don't do this! Actually, I'd recommend an Index Rebuild after each data shrink operation! –  marc_s Nov 22 '12 at 13:36

1 Answer 1

Paul Randal answers your question here:

Data file shrink should never be part of regular maintenance, and you should NEVER, NEVER have auto-shrink enabled. I tried to have it removed from the product for SQL 2005 and SQL 2008 when I was in a position to do so - the only reason it's still there is for backwards compatibility. Don't fall into the trap of having a maintenance plan that rebuilds all indexes and then tries to reclaim the space required to rebuild the indexes by running a shrink - that's a zero-sum game where all you do is generate a log of transaction log for no actual gain in performance.

So what if you do need to run a shrink? For instance, if you've deleted a large proportion of a very large database and the database isn't likely to grow, or you need to empty a file before removing it?

The method I like to recommend is as follows:

  • Create a new filegroup
  • Move all affected tables and indexes into the new filegroup using the CREATE INDEX ... WITH (DROP_EXISTING) ON syntax, to move the tables and remove fragmentation from them at the same time
  • Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)
share|improve this answer
    
Thank you Sir... for your valuable suggestion...I really appreciate it. –  Sourav Mondal Dec 1 '12 at 8:02
    
Thank you Sir... for your valuable suggestion...I really appreciate it. –  Sourav Mondal Dec 1 '12 at 8:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.