Create a SQL Server Maintenance Plan
NOTE: Although this example is for the Active Administrator database, this article can also apply for any product that uses a SQL database.
Open SQL Management Studio, expand the Management node, right click on the Maintenance Plans node and select “Maintenance Plan Wizard” from the context menu.
Click “Next" when the wizard starts.
Enter a name and give a description of what the plan does. Click the “Change” button to open the scheduler, (figure 2).
Enter a name for the job schedule sub plan, select “Recurring” for the scheduler type and check the “Enabled” check box. Configure the other setting as per your environmental requirements, the more data being collected, the more frequently the maintenance should run. Click “OK” when done. The SQL Server Agent service must be running before a Maintenance Plan can be scheduled.
The configured schedule should be visible in the scheduled field at the bottom of the screen. Click “Next” to continue.
The minimum recommended task for database optimization is “Check Database Integrity”, “Rebuild Index”, and “Shrink Database”. A full backup is also recommended for disaster recovery. In this example, the minimum tasks are selected, (figure 5).
The order that each task is executed is important (figure 6).
The first task should be to Check Database Integrity:
This task checks the allocation and structural integrity of all the objects in the specified database and repairs errors if necessary.
The next task should be Rebuild Indexes:
The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause applications to respond slowly.
The next task should be Shrink Database:
This task will remove the unused space from the database's files. De-allocate that space and let the O/S do what it needs with it. This will prevent problems especially if autogrowth is not enabled.
To select the Active Administrator database for the Rebuild Index task, click the drop down selection button for the list box to the right of the database field. Click the drop down list box to the right of the object field to select the targets of the index rebuild, in this example “Tables and views” is selected, (figure 7).
Check the Active Administrator database and click the “OK” button, (figure 8).
Repeat the same procedure as above to select the Active Administrator database for the Integrity Check task, then click “Next”, (figure 9).
Repeat the same procedure as above to select the Active Administrator database for the Shrink task, then click “Next”, (figure 10).
This section allows a report to be created and sent to a selected Operator. Check the “Write a report to a text file” check box and enter a new path if the default path is not acceptable. If an email report is desired, check the “Email report” check box and select an operator from the drop list, (figure 11).
Note: To send emails, the SQL Server Agent must be configured to use Database Mail or SQL Mail to send e-mail and pager notifications to operators. The instruction for doing this is provided in another document.
Click “Finish” to close the summary screen, (figure 12).
Click “Close when the wizard completes, (figure 13).