Brad’s Sure Guide to SQL Server Maintenance Plans- P53: 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 19 Create and Modify Maintenance Plans Using the Designer Figure Configuring the Reporting and Logging screen can be done any time throughout the Maintenance Plan design process although I have saved it for last. For example in the Maintenance Plan we just created a report text file will be written to disk every time a subplan executes. The Daily Full Backup subplan will be executed once a day 7 reports per week the Hourly Log Backup subplan will be executed 24 times a day 168 reports per week and the Weekly Maintenance subplan will be executed once a week 1 report per week . If you need to do any troubleshooting there will be a lot of text file reports to wade through. Fortunately each report has a time stamp and you should be able to narrow down a problem to a specific time period which will make it easier for you to find any reports for troubleshooting purposes. And as you can see this is why you need to delete older text file reports as part of your maintenance tasks as their numbers can add up fast. 261 Chapter 19 Create and Modify Maintenance Plans Using the Designer Save the Maintenance Plan Once we are done creating the Maintenance Plan we can save it by clicking on the Save Selected Items icon on the SSMS toolbar. Or if we try to exit the Maintenance Plan without saving it first we will be prompted to save the plan. Either method will ensure that the plan is saved. Test the Maintenance Plan Having created our Maintenance Plan it s important to test it before scheduling it and putting it into production. If a Maintenance Plan has only one subplan it can be started by right-clicking on it and selecting Execute. Unfortunately this does not work if a Maintenance Plan has two or more subplans which is the case in our example. Behind the scenes the Maintenance Plan is implemented as a single SSIS package containing a number of distinct executables one for each subplan and each executable is executed using a separate SQL Server Agent job. So if .
