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?