I am new to Sqlserver 2005 and I have created the maintenance plan where about 10 databases are being backedup using the maintenance plan described in the following link: http://www.mssqltips.com/tip.asp?tip=1094 However I have not used all the steps described in the plan as I am not sure what would be the significance of each plan. In my plan I have Back Up Database Task and Maintenance Cleanup Task. What are the other tasks I need to include in the maintenance plan? If anyone can send me some advice that would be a big help.
If you have your databases in full recovery mode, you need to include log backups as well. Otherwise your logs will grow constantly. You can read more about this here: http://www.sqlservercentral.com/articles/64582/
You probably want to clean up the history at some point in time. This removes the logs of the maintenance plan. I'd probably keep a month around and remove them on a rolling basis.
As far as maintenance, the reindex task is good to run periodically. Maybe weekly if you don't understand what it does. once you gain more experience, you might think about using your own reindex procedure that is a little smarter and running that weekly as well. We have a few scripts here: http://www.sqlservercentral.com/Scripts/Index+Management/
answered Nov 30, 2009 at 04:41 PM
Steve Jones - Editor ♦♦