Brad’s Sure Guide to SQL Server Maintenance Plans- P16: SQL Server has a reputation as being a simple database application to install, configure, and maintain. This is a little misleading. SQL Server is a powerful relational database that can handle the needs of the largest organizations and, as such, its proper maintenance almost certainly requires the attention of an experienced DBA. | Chapter 3 Getting Started with the Maintenance Plan Wizard You can double-click on each job to open it up and see what it looks like. While I don t recommend you make any changes it is interesting to look at the command used to execute the SSIS package for a particular scheduled task as shown in Figure . Figure This screen shows how the SSIS package will be executed for this maintenance task. As you can see in this figure when a scheduled job executes all it does is execute the related SSIS package with a number of parameters that specify the plan to be run the server it is to be run on and so on. In this particular example Subplan_1 of the Maintenance Plan is being run and so is passed in as a parameter. A word of warning before we move on while it s possible to customize your Maintenance Plan by directly modifying its SQL Server Agent jobs I strongly advise against it. Unless you are an expert in both SSIS and the SQL Server Agent the odds of breaking the Maintenance Plan are very high. If you need to make any changes to a Maintenance Plan use the Maintenance Plan Designer. In this way any changes will automatically be reflected in the Maintenance Plan s related SQL Server Agent jobs. 76 Chapter 3 Getting Started with the Maintenance Plan Wizard Likewise if you want to delete a Maintenance Plan be sure you do so right-clicking on the Maintenance Plan s name and clicking Delete. If you try to delete the Maintenance Plan s SSIS package or SQL Server Agent jobs directly you could create a mess that will be difficult to untangle. Generally speaking if you feel you need to do a lot of customization to a Maintenance Plan once it has been created then most likely you would be better off using T-SQL or PowerShell scripts for your database maintenance. Testing Your Maintenance Plan Having created a new Maintenance Plan and before congratulating ourselves on a job well done and going home for the night we should first test it to see if it runs as we expect. To .
