Currently we are using SQL 2008 for our development databases. We are planning to schedule maintance plan. so, please suggest the sequence of the maintance tasks.
Thanks in advance, Veejay.
asked Aug 12, 2010 at 08:35 AM in Default
Dont shrink the database, If your databases are in Full recovery mode make sure u take log backup along with full backup. If you dont want point in time recovery change the recovery model to simple, that way you dont need to maintain the logs. you can download and use this free super awsome script by Ola Hallengren for all your maintenance needs: SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization
Here is an article on how to use the scripts.
DaniSQL has a good answer, and in particular I agree you should not automatically shrink the databases. Personally, I do database shrinking only if there is an actual need for the space, I do it manually, and I then start requesting additional drives.
For very basic maintenance you can use the wizard to build a maintenance plan (I am assuming you are using SQL Server 2005 or later since you didn't specify). You loose a lot of control that way and that will cause problems in complex environments, but for a fairly simple environment with a true dedicated maintenance window, you can use the wizard and expect it take care of your needs for a while.
For more complex environments, well entire books have been written about that. In particular, you may want to look at Brad's Sure Guide to SQL Server Maintenance Plans (DBA Handbooks) (http://www.amazon.com/Brads-Guide-Server-Maintenance-Handbooks/dp/1906434344/ref=sr_1_4?ie=UTF8&s=books&qid=1281648942&sr=8-4)
answered Aug 12, 2010 at 02:46 PM