SQL Maintenance Plans - Under the Covers!


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


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


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.


4 (1)




4 (1)