Keeping your SQL Server running smoothly is extremely important for ensuring optimal performance and preventing possible business disruptions, and that's where regular maintenance plans come into play. Although SQL servers offer many maintenance tasks (database integrity, shrink database, reorganize index, etc.), we'll focus on "Rebuild Index", "Update Statistics," and "Clean Up History." These tasks help improve query performance, ensure accurate data retrieval, and manage server resources effectively.
By following the steps below, you can proactively manage your server's health and avoid costly downtime, making maintenance plans an indispensable part of your database management strategy.
11 Simple Steps on How to Set Up a Job
Step #1 - Maintenance Plan Wizard
In SQL Management Studio, in the Object Explorer, click on the Server icon to expand the list, then click on "Management", then right-click on "Maintenance Plans". In the drop-down menu, choose “Maintenance Plan Wizard.” Click "Next."
Step #2 - Configure the job schedule
The next step is to configure the job schedule, including name, schedule type, frequency, and duration. In the example below, I have entered the following:
- Name = Weekly Maintenance Plan
- Schedule type = Recurring, Enabled = Checked
- Frequency
- Occurs = Weekly
- Recurs every = 1 week on Sunday
- Daily Frequency
- Occurs once at = 2:00AM
- Duration = No end date
Step #3 - Select Maintenance TAsks
The next step is to choose which tasks to run. As I mentioned in the beginning, we will be running the Rebuild Index, Update Statistics and Clean Up History maintenance tasks.
Step #4 - Select Maintenance Task Order
Now, you can choose the order in which the tasks will run. In the example below, I have chosen from first to last: Rebuild Index, Update Statistics, and then Clean Up History.
Step #5 - Define Rebuild Index Task
In this step, you have to select the options that you'd like the Rebuild Index Task to perform. In the example below, I have entered the following:
- Databases = All databases
- Free space options = Default free space per page
- Index Stats Options
- Scan Type = Fast
- Optimize index only if
- Fragmentation > 30%
- Page Count > 1000
Step #6 - Define Update Statistics Task
As you just finished in the previous task, now you're selecting the options for the Update Statistics Task. In the example below, I have entered the following:
- Databases = All databases
- Update = All existing statistics
- Scan type = Full scan
Step #7 - Define History Cleanup Task
And for the last maintenance task chosen in this example, we'll select the options for the History Cleanup Task. In the example below, I have entered the following:
- Backup and restore history = Checked
- SQL Server Agent job history = Checked
- Maintenance plan history = Checked
- Remove historical data older than = 4 Weeks
Step #8 - Select Report Options
In this step, you can choose whether to save or distribute/email a report generated by the maintenance plan actions. This report is run after the tasks are complete and is not required.
Step #9 - Save the Maintenance Plan
Now, you can save the maintenance plan you've just created.
Step #10 - Verify Configuration
Now we need to verify that the maintenance plan was saved and created successfully. Navigate back to the main menu, go to the Maintenance Plans folder, and choose the maintenance plan you've just created. Right-click on it and select "Execute."
Step #11 - Review Completion
After the plan completes, right-click the plan again and choose "View History" to review the maintenance plan and verify the success of your created maintenance plan.
