How to Prevent Sql Server Jobs to Run simultaneously

Question

I have some jobs for example : job1, executing every 2 minutes job2, executing every 10 minutes job3, executing every 15 minutes

now there is a problem. jobs may occur simultaneously and cpu usage go to 100%;

is there a solution? remind that I need jobs to run approximately in their appropriate period.

thanks.

Answer 1

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.