Funny how things start out simple and management, then as time goes by you
realize you have a lot of stuff to deal with and the original system just isn't
working out! Jobs are a place where that can happen before you know it so this
week I thought I'd share some ideas on ways to use and manage jobs more
effectively.
If you view your existing jobs in Enterprise Manager you see that category is
a great place to start organizing, though you can also it from TSQL (look at
syscategories along with sp_add_category, etc) or DMO. SQL comes with quite a
few built in categories. In the screen shot below you can see I've got jobs
created by a maintenance plan and I've got some replication jobs.
Did you know you can add your own categories? It's easy to do via EM:
I'd recommend that you not remove any of the default categories, or that you
change the categories on jobs created by wizards in EM (maintenance plans,
replication). The job would still work, but the process that revises it or looks
for it may be including the category as part of the criteria. Nothing wrong with
adding more though. This is where stepping back and looking at all your existing
jobs may help you come up with a few categories to get you started. For
instance, I always add a DBA category for jobs I set up for tasks outside of
maintenance plans. In some cases I have a category for a specific application so
I can find all the jobs associated with it, or the category might be the
database name. You're looking for ways to make it easier for you (and everyone
else) to find stuff.
Categories aren't your only tool of course. The description block (below with
comment inserted by SQL automatically) is a great place to put why you're doing
it, what to do if it fails, who to contact, etc. If you have more info than you
can fit in the description (limited to 512 chars), put in the file name or URL
to a document that has all the info.
Not every job is a single step. Most replication jobs consist of three steps.
This is where a good naming convention for your steps pays off. In the
example above, it's very easy to see what is being done. Sometimes that still
won't be enough, so you'll open the step itself. You only get 3200 chars for a
step, but most steps aren't nearly that big. Plenty of room to add a comment or
two if it will help 2 months or 2 years later!
Speaking of good naming conventions, the name of your job is the place to
start. Put a good descriptive name, be wordy! Yes, you may end up having to
rearrange your EM window a bit to see it all, but it'll be there when you need
it. Remember you always have the option of querying sysjobs or sysjobsteps
directly if you're having a problem finding the job that does X or all jobs that
do Y.
There's more to cover, but we'll save something for next week. Start
experimenting with categories, review your job names, and then if you have time,
take a look at the article Nightly
Failed Jobs Report by Gregory Larsen.
One other thing - please rate the article (we use it to customize the 'Others
that read this also liked' section) and while you're at it, add a comment! Did
you learn anything? Disagree? Got a technique or idea I didn't cover?
