9. In the Define Reorganize Index Task select the Databases against which you want to execute this task and click the check box for Compact large objects and then click Next to continue with the wizard.
10. In the Define Update Statistics Task screen select the databases against which you want to execute this task and then select the appropriate option for Update and Scan type. Click Next to continue with the wizard.
11. In the Define Maintenance Cleanup Task screen select Maintenance Plan text reports & Search folder and also select Delete files based on an extension options. As you could see that for search folder and delete….option I have provided the folder location where the maintenance plan report files are stored and in the file extension “txt” value is provided. Finally on the same screen I have chosen the file retention period as one week. Click Next to continue with the wizard.
12. In the Define History Cleanup Task check all the option and set the retention period as one week to remove older files. Click Next to continue with the wizard.
13. In the Select Report Options screen click on Write a report to a text file option and provide the folder location where the report needs to be saved. Click Next to continue with the wizard.
14. In the Complete the Wizard screen you could see the summary of all the options which you have selected so far in the maintenance plan wizard, click Finish to complete the wizard.
15. In the Maintenance Plan Wizard Progress screen you need to make sure that all the tasks have completed successfully and then click Close to complete the wizard.
Executing the Newly Created Maintenance Plan
Once the “Basic Maintenance Plan for AdventureWorks databases” is created successfully you can execute the package either by right clicking the plan and then clicking Execute under the Maintenance Plans. Or you can expand the Jobs under SQL Server Agent node and execute the following job “Basic Maintenance Plan for AdventureWorks databases.Subplan_1”.
Conclusion
It is very important for a database administrator to always keep the databases healthy. If the databases are not maintained regularly then it is going to directly affect the query performance thereby resulting in slowing down applications which are using the databases. In this article we have seen how to create a basic maintenance plan using the wizard to perform certain maintenance tasks to ensure that database is kept healthy.
]]>