Maintenance Plan - Best Practice Questions

I am setting up maintenance plans for my SQL database and I have some
questions and want to see what other people do, so here I go.
What I do Now:. I have set up a Nightly plan to do full backups of specific
databases. I keep a 5 day history of .bak files that eventually get deleted
via a Maintenance Cleanup. I have a Weekly maintenance plan that 1) Rebuilds
Indexes, 2) Update Statistics, 3) Shrink Database and 4) Check Database
Integrity. Here are my questions.
1. I do a Full backup of my specific databases but I don't do Transaction
Logs. Is this OK or should I do transaction logs as well? What additional
benefits would this give me?
2. Do people backup any of the System databases? How would they benefit me
if I end up having to build a new server and reinstalling SQL 2005? I just
want my user databases back.
3. What is the difference between Rebuild and Re-Organize Indexes? Which
do you select and why?
4. Outside of the obvious benefits of "Shrinking" a database to eliminate
any unused database space and producing smaller .mdf files why would I want
to do this if I have a threshold of 50mb for growing .mdf files? Would I not
be defeating the purpose and really costing my server more time to allocate
additional space when I hit that threshold?
5. I don't do Cleanup of History just Cleanup Maintenance Task to get rid
of old .bak files, should I and what benefits do I get?
6. For my Weekly Maintenance tasks should I run them in any specific order
or just take the SQL Server default ordering?
7. I run the Weekly Maintenance tasks during the weekend. Should I be
running them more often than weekly?
Thanks for any input. I have a very good handle on things but would love to
hear more 2c from fellow SQL administrators on what they do for "Best
Practices"
-Richard KSome opinions:
> 1. I do a Full backup of my specific databases but I don't do Transaction
> Logs. Is this OK or should I do transaction logs as well? What additional
> benefits would this give me?
We can't answer this, you can. How much data can you afford to lose? It it OK with all smiling faces
if you lose one days worth of data?
> 2. Do people backup any of the System databases? How would they benefit me
> if I end up having to build a new server and reinstalling SQL 2005? I just
> want my user databases back.
Yes, we do. Basically because there is information in the system databases. Im aster, you have
things like logins, linked servers, sp_configure settings, user-defined error messages etc. In msdb,
you have things like jobs, alerts, operators, possibly SSIS packages.
> 3. What is the difference between Rebuild and Re-Organize Indexes? Which
> do you select and why?
See http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. The article is for
2000, so you have to translate the commands, but basics applies to 2005.
> 4. Outside of the obvious benefits of "Shrinking" a database to eliminate
> any unused database space and producing smaller .mdf files why would I want
> to do this if I have a threshold of 50mb for growing .mdf files? Would I not
> be defeating the purpose and really costing my server more time to allocate
> additional space when I hit that threshold?
Yes, you seem to get this. Don't shrink, quite simply. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> 5. I don't do Cleanup of History just Cleanup Maintenance Task to get rid
> of old .bak files, should I and what benefits do I get?
Not removing history will make the history tables very large over time.
> 7. I run the Weekly Maintenance tasks during the weekend. Should I be
> running them more often than weekly?
I prefer to run integrity check as often as I can.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:645A3632-9672-4F11-8278-76B4FF1E082B@.microsoft.com...
>I am setting up maintenance plans for my SQL database and I have some
> questions and want to see what other people do, so here I go.
> What I do Now:. I have set up a Nightly plan to do full backups of specific
> databases. I keep a 5 day history of .bak files that eventually get deleted
> via a Maintenance Cleanup. I have a Weekly maintenance plan that 1) Rebuilds
> Indexes, 2) Update Statistics, 3) Shrink Database and 4) Check Database
> Integrity. Here are my questions.
>
> 1. I do a Full backup of my specific databases but I don't do Transaction
> Logs. Is this OK or should I do transaction logs as well? What additional
> benefits would this give me?
> 2. Do people backup any of the System databases? How would they benefit me
> if I end up having to build a new server and reinstalling SQL 2005? I just
> want my user databases back.
> 3. What is the difference between Rebuild and Re-Organize Indexes? Which
> do you select and why?
> 4. Outside of the obvious benefits of "Shrinking" a database to eliminate
> any unused database space and producing smaller .mdf files why would I want
> to do this if I have a threshold of 50mb for growing .mdf files? Would I not
> be defeating the purpose and really costing my server more time to allocate
> additional space when I hit that threshold?
> 5. I don't do Cleanup of History just Cleanup Maintenance Task to get rid
> of old .bak files, should I and what benefits do I get?
> 6. For my Weekly Maintenance tasks should I run them in any specific order
> or just take the SQL Server default ordering?
> 7. I run the Weekly Maintenance tasks during the weekend. Should I be
> running them more often than weekly?
> Thanks for any input. I have a very good handle on things but would love to
> hear more 2c from fellow SQL administrators on what they do for "Best
> Practices"
> -Richard K
>