SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Managing Jobs - Part 1

By Andy Warren,

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?

Total article views: 10455 | Views in the last 30 days: 8
Related Articles


How much detail should be used in comments, do you assume a level of understanding? One comment I ...


SQL 2005 SP in Master DB With Category 2

SYOBJECTS Category Question


Get products ordered by category order

Products ranked by category ranked


Some Comments about our Discussion Area

Sometimes as hard as you look you just can't find the answer or idea you're looking for - that's whe...


A look at comments

Everyone knows that we should include comments in our code right? On the other hand the vast majori...

database design    
naming standards    
sql server 7