Thank this author by sharing:
By Andy Warren,
2005/03/04 (first published: 2002/04/11)
I imagine at least half of you will disagree with me, but I find the Database
Maintenance Plans to be a worthwhile tool. I guess the biggest drawback (other
than a bug or two early on) is that it's a black box. I think we'd all be
happier if we could see what it's doing! Still, one of the nice things about the
black box approach is that if you're using a maintenance plan, you'll always get
the same results.
Could you accomplish the same thing without the wizard approach? Sure! Let's
take a look at everything it does:
Doable, but a fair amount of code to get it all right and working solid. We
can shorten the time if we skip log shipping (not as commonly used) and pull
some handy scripts out of the library.
Now be fair when you answer this question - would something we built be
"better" than the wizard, or are we just happier because we can see
the code? Or do you find that the plans just don't go far enough? There are a
couple things I think should be included:
Of course if I could have it all, I'd include a few more things:
Got things you'd add to that list? What would it take to make it the perfect
tool for you? Or at least good enough to use? Think about that while you finish
the rest of the article!
So how do maintenance plans work anyway? When you create a plan a row gets
inserted into MSDB into the sysdbmaintplans table. Then depending on which
databases you selected it to run against, one or more rows get added to
sysdbmaintplans_databases. The column database_name will contain either "All Databases",
"All System Databases", or "All
User Databases" if you selected one of the first three options. If you
selected databases manually then the name of each database gets added to the
table, each row having the same plan_id that links back to sysdbmaintplans. Want
to see what jobs are associated with a plan? Just look in sysdbmaintplan_jobs.
All the history is contained in sysdbmaintplan_history.
Tip: If you create a plan that is based on one custom selection, you can
then add/remove more database names to the plan by editing
sysdbmaintplans_databases directly. You just need one row per database along
with the associated plan ID. The wizard doesn't know the difference! This is an
easy way to achieve the ability to use a select statement to indicate which db's
should be covered by the plan.
That explains a little about where, but not how, right? All the magic is
contained in xp_sqlmaint, an extended stored procedure for which we can't view
the code, but a little browsing of the process list indicates that it really
just passes the parameters on to sqlmaint.exe. I created a plan on my test
machine, here is what I ended up with for the backup job:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0D097FED-F1C1-4850-A34C-7A83EF96A81B -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -BkExt "BAK"'
Have you looked at SQLMaint? Here are the options it supports directly from
Finally, exactly how do the entries get added to the tables I mentioned
above? The wizard generates a surprisingly small amount of code, the biggest
part will look something like this:
DECLARE @PlanID nchar(36)
SELECT @PlanID = N'CF635C1C-F486-442B-B63F-8B7BD62A1003'
DECLARE @ReturnCode INT
IF (SELECT COUNT(*) FROM msdb.dbo.sysdbmaintplan_databases WHERE plan_id = @PlanID AND database_name = N'DefaultDB') < 1
INSERT msdb.dbo.sysdbmaintplan_databases (plan_id, database_name) VALUES (@PlanID, N'DefaultDB')
DELETE FROM msdb.dbo.sysdbmaintplan_jobs WHERE plan_id = @PlanID
INSERT msdb.dbo.sysdbmaintplan_jobs VALUES (@PlanID, N'2E8A5F49-28BB-42AF-BEED-53F2575716AA')
INSERT msdb.dbo.sysdbmaintplan_jobs VALUES (@PlanID, N'95C94859-C869-472B-AE02-6DB220E5C177')
INSERT msdb.dbo.sysdbmaintplan_jobs VALUES (@PlanID, N'218DAFDA-F5F1-49D1-A008-8FB89E3AE6B2')
Nope, not fun to look at. But it works! Seriously, I'd like to hear from you.
Do you use the plans or not? Why or why not? What would you like to see added or
changed? Post a note in the attached discussion area, I normally reply to posts
the same day.
Execution error while executing the procedure
URGENT: Error executing scripts......
Error executing the package , pls help.
Error accessing Active Directory form code
Design Active Directory as a database
As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.