Pedro Azevedo Lopes shares his prescription for a better-looking SQL Server in his post About Maintenance Plans – grooming SQL Server:
I admit I’m not a big fan of Maintenance Plans (the feature that goes by that name) mainly because of its “do or die” approach. So, it was not by chance that I wrote and keep updating the AdaptiveIndexDefrag procedure. Good maintenance (or lack thereof) may be the difference between good and bad performance. Following up on that train of thought, I’ve been meaning to share 6 scripts that I always use to automate maintenance tasks in SQL Server. In a couple minutes I have deployed a comprehensive set of SQL Agent jobs that will get the work done.
As we all know, servers that think they’re looking better feel better about themselves.
Having these (or other) scripts to deploy on your SQL Servers saves you the trouble of implementing Maintenance Plan objects, and gives you much better control and flexibility on what is happening on your maintenance cycles. After these have been running on a regular basis, you will know that your SQL Server is being proactively managed on what are the most common maintenance tasks. Of course all the logs should be reviewed regularly so that issues can be corrected.
Pedro’s collection of scripts (provided as-is – please see his disclaimer) span many aspects of SQL Server maintenance, and should keep your SQL Server’s coat glossy, and its teeth healthy.
Posted by Tristan Kington, MSPFE Editor, former winner “Best in Show, TPC-Coat”