Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 10436 | Views in the last 30 days: 8
 
Related Articles
FORUM

Commenting

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

FORUM

SQL 2005 SP in Master DB With Category 2

SYOBJECTS Category Question

FORUM

Get products ordered by category order

Products ranked by category ranked

ARTICLE

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...

BLOG

A look at comments

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

Tags
administration    
configuring    
database design    
miscellaneous    
monitoring    
naming standards    
programming    
sql server 7    
sql-dmo    
strategies    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

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.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones