Chapter 25. Working with maintenance plans
Just like a car or a house, you want to maintain your databases so that they run smoothly and safely over time. You probably have actions you want to perform that are specific to your application and data, but some actions are common to most databases, like backups and defragmentation. This is why Microsoft created maintenance plans that make the implementation of standard actions easy. We specify which of these standard actions we want to perform, for which databases, and at what intervals—no T-SQL coding required. Of course, some DBAs want more control over the actions than the maintenance plans allow for, but for those who are not full-time DBAs, maintenance plans are a good solution.
The implementation of maintenance plans (MPs) has changed over time. From SQL Server 6.5 to 2000, an MP was executed by sqlmaint.exe through a SQL Server Agent (Agent) job. As of SQL Server 2005, an MP is a SQL Server Integration Service (SSIS) package, executed by an Agent job.
You create a plan using SQL Server Management Studio (SSMS) and right-clicking the Maintenance Plans folder under the Management folder. You can use either a wizard or a trimmed-down version of the SSIS package Designer, available inside SSMS. For beginners, I recommend using the wizard, and, as you get more comfortable using MPs, you can dive into the Designer.
