如何防止Sql Server作业同时运行 [英] How to Prevent Sql Server Jobs to Run simultaneously
问题描述
我的SQL代理中有一些计划的工作:
I have some scheduled jobs in my SQL Agent:
- Job1每2分钟执行一次
- Job2,每10分钟执行
- Job3每15分钟执行一次
可以看到,多个作业可以同时运行。当这些作业同时运行时,会导致CPU使用率达到100%。
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.
推荐答案
使用会话锁定通过 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.
这篇关于如何防止Sql Server作业同时运行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!