Dependencies between SQL Server Jobs

[SteveJBoyle]

I am the network administrator for my company, and by default our DBA as well. I am certainly not a SQL expert, but have been confronted with a fairly large problem that I'd like some help on. We have a SQL server that serves data out to about 200 websites. Besides serving data, their are many SQL jobs that run nearly constantly, some on a schedule and some as triggers launched by table updates. Each of these jobs runs fine on its own, but there are times when multiple jobs running at the same time will lock trying to access data. This causes a slow-down of the jobs, as well as the websites, and usually leads to angry users and widespread chaos .

We've been able to isolate which combinations of jobs are most likely to cause problems when run together and I was wondering if their is a way to setup any sort of dependencies/exclusions between jobs (always run job #6 right after job #1, never run job #2 at the same time as job #4). Our programmers have said that they can probably do this in code, but I was hoping SQL had some built in method.

BTW: We are running SQL Server 2000 Enterprise Edition w/SP3A.

Thanks for all of the help.

-Steve Boyle

 

[Catadmin]

DTS actually has a Precedence level that can refuse to let job 6 run until job 1 has run.

Additionally, when going to Management -> SQL Server Agent -> Jobs, when creating new or editing old jobs, you can insert multiple steps that can has "On Success" and "On Failure" options. The options you can choose are "Quit Job reporting Success", "Quit Job reporting Failure" and "Goto Next Step".

You can find more information on this in Books Online under DTS and Job...

Does this help?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[mrdenny]

There is no presedence that can be set between jobs in the SQL Server agent. This would need to be handled via code within the job.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)