Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
The History Channel
I won't display each screen used in creating a maintenance plan, but the screen that's important to this discussion is shown in Figure 1.
){kind=link}
Figure 1. Storing the maintenance history in a table
As you can see, the wizard stores the history of its activities in the sysdbmaintplan_history table in the msdb system database. In fact, the whole maintenance plan, jobs, steps and so forth are stored in the msdb database. I'll use the history table, along with the Web Assistant Wizard, to create an HTML page that refreshes each morning to show the status of the plan.
Listing 1 shows a query of the sysdbmaintplan_history table from my server.
Listing 1. Querying the BLANK Table
Table 1 shows the breakdown of the columns in this table.
Table 1. sysdbmaintplan_history Column Breakdown
|
Column |
Use |
|
sequence_id |
Key field for this table—sort of an internal tracking number. |
|
plan_id |
The number of the maintenance plan. |
|
plan_name |
The name of the maintenance plan. |
|
database_name |
The name of the database affected by the operation in the "activity" field below. |
|
server_name |
Name of the server performing the maintenance. |
|
activity |
The particular activity |
|
succeeded |
This number determines whether the activity worked or not. 1 is good; 0 is bad. I know, BOL says different, but try it and see! |
|
end_time |
The stop time of the activity. |
|
duration |
How long the activity took. |
|
start_time |
The start time of the activity. |
|
error_number |
The error number (if any) generated by the activity. |
|
message |
The messages (if any) produced by the activity. |
Some of these columns are more interesting for this task. I'll use this table to refine the query, as shown in Listing 2. I'll also change the order, so that the columns that are important are displayed first and then sort the results from the latest results to the earliest. This way, I can quickly scan the Web page to see if the recent activities were successful.
Listing 2. Revised Query
SELECT succeeded , database_name , activity , start_time , end_time , duration , error_number , message FROM msdb.dbo.sysdbmaintplan_history ORDER BY start_time DESC, database_name
Note that you can select different columns from those I used, and you can sort or group them to your liking. You can also do some fancy limitations to get only yesterday's data.
Now that I have the refined query, I can plug it in to the Web Assistant Wizard.