CODEWITHSUNDEEP
sql-serverconcurrencysql-server-agent
ehsan
ehsan

Reputation: 787

How to Prevent Sql Server Jobs to Run simultaneously

I have some scheduled jobs in my SQL Agent:

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.

Upvotes: 9

Views: 5108

Answers (1)

gbn
gbn

Reputation: 432657

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.

Upvotes: 13

Related Questions