Preventing a job from running twice simultaneously

Question

Post reply

Preventing a job from running twice simultaneously

harsh.vm

Old Hand

Points: 372
More actions
April 16, 2010 at 7:35 am

#139873

Hi all,
Please help me out with the following issue. I have a job created on my local machine. I need to prevent it from running twice simultaneously. If user tries doing that, user should be given appropriate error message.
For example, I can run the same job from my application as well as from backend. This has to be prevented.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

make use of a table, with a 0 and 1 value, with date.

each time the job has started, it writes a date and value of 1 in the table.

configure the job to refer to the table first, IF the there is an entry in the table with today's date, AND a value of 1, then stop, rollback etc.

If there is no entry for the date, or if the value is 0, then it can proceed.

make sense?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

harsh.vm Old Hand Points: 372 More actions · Answer 2

Thanks a lot Henrico. But the case you have explained works fine if you are running job at different times. What will be the output if the job has started running twice at a time.....

I hope you got my point. Also, if job is not completed it won't update the status in the table that we use for monitoring job statusright???.....

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

A job can only run once at a time.

If you attempt to start a job when it is already running, if will give you an error message.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Yes, Michael's on top of this. If a SQL Agent Job is actively running, you cannot call it and start it again.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

harsh.vm Old Hand Points: 372 More actions · Answer 5

Thanks all......

One more thing I wanted to know is, how do you check the status of running job? Because I want to check for status of job and if it's running then, I want to show some valid error message in my application. Can it be done through some query or is there any table in MSDB where I can check for status of running job???

Please let me know. Thank you....

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 6

Hi

I think the below two sys tables in MSDB will help you. You can filter it for your Jobname

select * from sysjobs

select * from sysjobservers

Regards,

MC

Thanks & Regards,
MC

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 7

To see what's running you go to sysjobactivity in the msdb database.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning