Menu
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.
511
Expand SQL Server Agent, expand Jobs, right-click the job you want to stop, and then click Stop Job. If you want to stop multiple jobs, right-click Job Activity Monitor, and then click View Job Activity. In the Job Activity Monitor, select the jobs you want to stop, right-click your selection, and then click Stop Jobs.
The SQL Server agent checks whether the job is already running before starting a new iteration. If you have long running job and its schedule comes up, it would be skipped until the next interval. You can try this for yourself. If you try to start a job that's already running, you will get an error to that effect.
Using SQL Server Management Studio Expand SQL Server Agent. Expand Jobs, and then right-click the job that you want to disable or enable. To disable a job, click Disable. To enable a job, click Enable.
Technically speaking, most SQL Server Agent Jobs (but not all) are batch jobs in the sense that they're a wrapped batch of commands or operations sent to be executed at various times against the server.
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.
143
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
