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 GROUP BY FactKeyA OPTION(ORDER GROUP) 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.
