Order for Maintenance Tasks

Question

Hello,
We are going through are back-up procedures and are cleaning them up. Right now we have a sub-plan for almost each task (backup database, Shrink Database, Check database Integrity). What we want to do is create one or two sub plans.

My question is: is there a preferred order of when to the run the task? Should we shrink the database first then back it up, or should we rebuild and reorganize the index first?

Thank you

Answer 1

Generally there is no such rules \ constraints.I would say the answer is -- it DEPENDS -- . You will be knowing better about your own environment like database usage , application load and hardware in which your sql runs .Make sure none of your maint plans are coinciding with each other. Do not run shrink database or checkdb often. There are no white paper on this because its purely an Infrastructure environment dependent stuff.

Should we shrink the database first then back it up, or should we rebuild and reorganize the index first?

Running ALTER INDEX … REBUILD and then running shrink to reclaim the space used for the rebuild has no(nasty) effect as the shrink may again fragment your index.
Also i would like you to have a look at the effects of shrinking a database in whole , written by PAUL RANDAL - http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx

---

Thanks, Leks

Answer 2

Don't shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi