SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Maintenance Plans - Under the Covers!

By Andy Warren,

A few weeks back I wrote about using Maintenance Plans. Had a better than expected response, though as you'd expect, not everyone agreed with me! If you have time, stop and read that first article and the discussion that goes along with it, it really sets the stage for this article.

Arguably the biggest complaints about the maintenance plans are the 'black box' nature of them and the relatively small amount of information provided when something goes wrong. So I thought it might be interesting to see what could be done to counter those complaints.

Let's start by looking at error reporting. For this first example I created a maintenance plan that just does a basic integrity check (DBCC) on Northwind. I ran the resulting job once to make sure it worked, then I changed the db to single user mode. When I ran the job a second time it failed. Right click to see job history gives you this:


Not very helpful. In this case we know why the job failed, but imagine the frustration trying to figure it out otherwise. Seems like there should be a better way? Sometimes there is. One place to start is by right clicking on the plan (not the job!) and selecting Maintenance Plan History. You get something like this:

I'll double click to see the details:

Now that's something we can use! Makes perfect sense if you think about it, the step history reflects the overall result, additional information gets logged to a separate table. Which brings us to another gotcha. You have to enable logging or you won't see anything in the plan history.

It may not always be enough, but sometimes it will be a lot better than nothing. Now let's see if we can peek inside the black box a little. Going back to my initial plan that consisted of one integrity task, I started Profiler to see what was going on. Leaving out some of the housekeeping stuff, this is the main part:

CREATE TABLE [##db_maint_plan_lock_db_Northwind] (col1 int)

use [Northwind]

SELECT @BlobEater = CheckIndex(FactKeyA + FactKeyB + Facts) FROM 
(SELECT TOP 100 PERCENT FactKeyA, FactKeyB, Facts FROM { IRowset 0xB8439543 } 
ORDER BY FactKeyA, FactKeyB) as SortedFacts 

dbcc checkdb WITH NO_INFOMSGS

DROP TABLE [##db_maint_plan_lock_db_Northwind]

Well, at least the DBCC makes sense. Not sure what the @Blobeater is! Anyone who knows what this checkindex function does, please enlighten us! Next I altered the plan to tell it to try to repair minor problems. I ran the job again, it failed. Digging into the plan history revealed this:

I had Query Analyzer open in Northwind which prevented the job from setting single user status. The core statements executed were as follows:

exec sp_dboption N'Northwind', N'single', N'true'

use [Northwind]

dbcc checkdb(N'Northwind', REPAIR_FAST ) WITH NO_INFOMSGS

So far no black magic in the black box. Well, other than the BlobEater! So far using Profiler reveals that the maintenance plans are just a wrapper around stuff we would/could do ourselves. In a follow up article I'll dig into some of the other options to see what can be found.

Total article views: 9209 | Views in the last 30 days: 7
Related Articles

Export Maintenance Plans history Sql 2000

How do I export Maintenance Plans History in SQL 2000


No job history displaying for maintenance plan

Job history will not display on jobs. Will only display on maintenance plan


RYO Maintenance Plan – History Cleanup

The last step in our campaign to rid the world of maintenance plans deals with housekeeping: the his...


Backup history

Backup history


can't query on 'Order Details' table in Northwind

Northwind - Order Details

backup and restore    
sql server 7