Creating a Basic Database Maintenance Plan
1. Connect to SQL Server 2008 Instance using SQL Server Management Studio.
2. In the Object Explorer, Click on Management and right click Maintenance Plans to open up the popup windows as shown in the below snippet. In the popup windows you need to select Maintenance Plan Wizard.
3. In the Maintenance Plan Wizard click next to continue with the wizard.
4. In the Select Plan Properties screen you need to provide the name & description for the maintenance plan. In the same screen there is an option available to choose the schedule. You can either have different schedules for each tasks or you can choose a single schedule for the entire plan.
5. Click on Change… to open up Job Schedule Properties screen as shown in the below snippet. Here you need to choose schedule type as Recurring as you want to run the entire plan once daily at 12:00 AM. Click OK to save the schedule information and finally click next in Select Plan Properties screen to continue with the wizard.
6. In the Select Maintenance Tasks screen choose the database maintenance tasks as shown in the snippet and the click Next to continue with the wizard.
7. In the Select Maintenance Task Order screen you can choose the order in which the tasks you have selected in the previous screen needs to be executed by the SSIS package once the maintenance plan is successfully created. In this plan we want Clean Up History task to execute as the last task, this can be done by selecting Clean Up History task and then clicking Move Down…. button to bring it down and make it as the last task to be executed by the maintenance plan, click Next to continue with the wizard.
8. In the Define Database Check Integrity Task select databases against which you want the task to be executed. This can be done by selecting the drop down list for databases and then selecting each of the databases against which you want this task to execute. In this example we will be running all the tasks against AdventureWorks, AdventureWorksDW2008 and AdventureWorksLT2008 databases. Click OK to save the list of database against which you want to run the tasks and also select Include Indexes check box. Click Next to continue with the wizard.
