A couple weeks back I wrote Managing

Jobs - Part 1 as a both a brief introduction to some of the features of jobs

plus a reminder that as your business grows and changes, so must the way you

manage your jobs. This week I'd like to continue that by talking about some

other ideas that you might consider to help you manage them more effectively.

Let's start by revisiting a subject I covered in Worst

Practice - Not Having An Archive Plan For Each Table. I've found that unless

I design a plan to purge data at some point, it will grow unchecked forever,

which is usually about 3 years too long. Trying to solve that problem for

myself, I started by creating a category called "Cleanup", then

creating one job per database labeled 'DBName - Daily Cleanup'. Then I create

one or more steps that usually just have simple things like "delete from

orderdetails where orderdate < getdate() -365". If they are all plain

deletes, I just put them in one step. If I've got portions that have

dependencies (like orders vs orderdetails) I might put that in a separate step

to make it easier to see. I like to run these jobs daily so that the amount of

data being moved or removed is manageable - why delete 300,000 rows at the end

of the month when you can delete 10,000 per day? One job isn't always going to

be enough, you may well need a monthly or end of year type job. In any case,

using 'Cleanup' they're easy to spot when I need to see what is being deleted

when or need to add a new step to the job.

A big big item you need to think about is who gets notified when. Take a look

at your options:

I've gone back and forth on email notification, but the plan I'm using now is

to send the email to the person who is responsible for fixing the problem.

Sometimes it may be that they'll just ask me to run the job again, other times

the data is bad and must be fixed first. The alternative is you could have all

notifications come to you. I've tried this and it's not bad, but sometimes I'm

busy and no one can start working until I tell them it failed. Don't be afraid

to add operators and send them the notifications, it's easy enough to change if

they move on.

Paging can be useful, but should be used lightly. On my Nextel phone there

doesn't seem to be a way to say 'delete all queued messages', I have to read

every one! Reserve pages for really urgent problems. Same goes for net send - I

typically only enable this when I'm troubleshooting a problem and might not

realize I've gotten email if I'm working on something else. Net send is a lot

faster than a page usually.

Writing to the event log? I run 400 or more backups a day, when you look in

the log you scroll...and scroll. Leave the event log for real problems, rely on

the agent and the job history for managing your jobs. It's easier, you always

have access to it, your admin will thank you.

The automatic delete option is not used often, but it's a great tool when you

need it. I use maintenance plans for index rebuilds, sometimes I'll want to do a

quick run on a single db over lunch if it seems unusually slow. I set up a new

plan rather than altering my existing one, then modify the job to delete itself

when done - so I don't forget and let it run at lunch for a week or two.

Managing jobs is tough. If you get email every time a job runs, you'll soon

get used to them and stop paying attention - until they stop showing up...maybe.

Failure notification is better to me, but you are at the mercy of SQL Agent. Not

uncommon for a job to hang, so it hasn't run, but hasn't failed either! For time

sensitive jobs I typically run a second job that checks the history of the first

job looking to see if it completed in time. This works fine until the agent

itself fails. Then what?

Let me digress here a moment to say that not all jobs run in SQL. You may

have jobs that run the tape backup via the NT scheduler or some other scheduler.

Ideally you want to monitor ALL those jobs. Something to think about.

I like Greg Larsen's idea of a failed job report. That works well with the

idea of sending the failure notice to the person responsible for fixing it, yet

you can still monitor to make sure problems are resolved. Beyond that, I've been

thinking that what I really want (and have not yet built) is a process separate

from SQL that checks on all my jobs across servers. In particular I'd like to be

notified if a job varies in run time from the last x times it ran. If a job

usually takes 5 minutes and all of a sudden takes 20 minutes, I need to know and

figure out why. I think I could also use this to catch jobs that hang up and

never finish.

In a week or two I'll wrap this up with a discussion about how to code

complicated jobs (exe, dll, vs., or TSQL) and where to run them. Until then, I

hope you've enjoyed reading this and will take a minute to comment on the

article - positive or negative!