How to Prevent Sql Server Jobs to Run simultaneously

Question

I have some scheduled jobs in my SQL Agent:

Job1, executing every 2 minutes
Job2, executing every 10 minutes
Job3, executing every 15 minutes

As you can see, multiple jobs can run simultaneously. When these jobs do run simultaneously, it is causing the CPU usage to go to 100%.

Is there a solution? Is there a way to control the number of jobs running concurrently? Note: I need these jobs to run approximately in their appropriate period.

You could use Mutexes to prevent more than one job running at a time - what language are you using to send the SQL queries to the DB? — SlappyTheFish, Oct 19 '10 at 12:58

gbn · Accepted Answer · 2010-10-19T14:25:20.693

13

Use a session lock via sp_getapplock

You're asking for user-controlled concurrency and this is usually the best way.

This allows you to wait or abort if the lock is already held by another job. We use it in one or 2 places to stop multiple users forcing the same task to run overlapping. It works well.

edited Oct 19 '10 at 14:25

answered Oct 19 '10 at 12:59

gbn

422,506
82
585
676

3

+1: Dam I wish I knew about this years ago. Previously, I had been rolling my own solution to this flavour of problem by using a status table to monitor the current state of a given process. – John Sansom Oct 19 '10 at 13:03
@John Sansom: Glad to be of service... If you can use Transaction locks it is self-releasing too. – gbn Oct 19 '10 at 14:26

How to Prevent Sql Server Jobs to Run simultaneously

1 Answers1

Linked