Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 324

6. In Select Maintenance Task Order, we can choose the order should these tasks
be performed. At this point, we will leave the defaults without changing the order
of tasks.
7. The next set of screens will present individual tasks as per the selection: Check
Database Integrity, Reorganize Index, Rebuild Index, Update Statistics, Back Up
Database (Full), Back Up Database (Differential), Back Up Database (Transaction
Log), Clean Up History, and Maintenance Cleanup Task.
8. At the screens Check Database Integrity, Reorganize Index, Rebuild Index, Update
Statistics, Back Up Database (Full), Back Up Database (Differential), and Back Up
Database (Transaction Log) at the Database(s) selection drop-box, choose All user
databases (excluding master, model, msdb, tempdb).
9. In the Define Clean Up History Task, select Backup and restore history, SQL Server
Agent job history, and Maintenance plan history options, and choose the Remove
historical data older than value as 3 Months.
10. Once the task screens are completed, the Maintenance Plan Wizard Progress
screen displays the process completion with a status as Success.
11. If there is a problem, it will be reported in the message column with a hyperlink. Click
Close to close the wizard.
12. The newly created maintenance plan will be scheduled automatically, which can be
viewed in the SSMS | Management folder | SQL Server Agent | Jobs.
This completes the steps to design maintenance tasks on a mission-critical environment.
How it works.
The maintenance plan tasks provide the required SSIS components for package deployment
that act as elements to perform required database maintenance activities.
All the defined tasks within the maintenance plan will use an ADO.NET connection manager
to connect. Then, the server instance will identify the relevant component based on the
connection manager on each plan task against corresponding databases.
All the corresponding tasks within the plan can be viewed by clicking on the View T-SQL button
on the maintenance task.
The maintenance plan tasks are executed using Execute Package Task within the SSIS
packages that are stored in sysdtspackages90 table in the MSDB system database. Further,
the following system-stored procedures are executed internally by the maintenance plan as
per the schedule:
F sp_clear_dbmaintplan_by_db
F sp_maintplan_close_logentry
F sp_maintplan_delete_log