問題描述
我目前正在使用這樣的代碼來檢測 SQL 服務器作業是否正在運行.(這是 SQL Server 2005，所有 SP)
I'm currently using code like this to detect if a SQL server job is running. (this is SQL Server 2005, all SP's)
return (select isnull(
(select top 1 CASE
WHEN current_execution_status = 4 THEN 0
ELSE 1
END
from openquery(devtestvm, 'EXEC msdb.dbo.sp_help_job')
where current_execution_status = 4 and
name = 'WQCheckQueueJob' + cast(@Index as varchar(10))
), 1)
)
沒有問題，一般來說，它工作得很好.
No problems there, and generally speaking, it works just fine.
但是....(總是一個但是)
But.... (always a but)
有時，我會調用它，返回作業未運行"結果，此時我將嘗試通過
On occasion, I'll invoke this, get back a "job is not running" result, at which point I'll try and start the job, via
exec msdb.dbo.sp_start_job @JobName
并且 SQL 將返回SQLAgent 拒絕啟動作業，因為它已經有一個掛起的請求".
and SQL will return that "SQLAgent has refused to start the job because it already has a pending request".
好的.也不是問題.可以想象，在這段代碼可以啟動目標作業之前，有一個小窗口可以啟動它，但是在檢查它是否啟動之后.但是，我可以將其包含在 try catch 中并忽略錯誤，對嗎?
Ok. Also not a problem. It's conceivable that there's a slight window where the target job could get started before this code can start it, but after checking if it's started. However, I can just wrap that up in a try catch and just ignore the error, right?
begin try
if dbo.WQIsQueueJobActive(@index) = 0 begin
exec msdb.dbo.sp_start_job @JobName
break
end
end try begin catch
-- nothing here
end catch
問題來了.
10 次中有 9 次，這很好用.SQL 代理將引發錯誤，它被捕獲，并且處理繼續進行，因為作業已經在運行，沒有傷害沒有犯規.
9 times out of 10, this works just fine. SQL agent will raise the error, it's caught, and processing just continues on, since the job is already running, no harm no foul.
但偶爾，我會在作業歷史記錄"視圖中收到一條消息(請記住上面的代碼來檢測特定作業是否正在運行，如果不是從另一個作業實際運行，則啟動它)說作業失敗，因為SQLAgent 拒絕啟動作業，因為它已經有一個待處理的請求".
But occasionally, I'll get a message in the Job History view (keep in mind the above code to detect if a specific job is running and start it if not is actually running from another job) saying that the job failed because "SQLAgent has refused to start the job because it already has a pending request".
當然，這正是 TRY CATCH 應該處理的錯誤！
Of course, this is the exact error that TRY CATCH is supposed to be handling!
當發生這種情況時，正在執行的作業就會終止，但據我所知不會立即終止，只是非常接近.我把日志記錄到處都是，沒有一致性.一次失敗，它會在 a 點，下一次在 b 點.在某些情況下，A 地和 B 地只有一個
When this happens, the executing job just dies, but not immediately from what I can tell, just pretty close. I've put logging all over the place and there's no consistency. One time it fails, it'll be at place a, the next time at place b. In some cases, Place A and place B have nothing but a
select @var = 'message'
在他們之間.很奇怪.基本上，該作業似乎被毫不客氣地轉儲，并且該作業中剩下的任何要執行的內容都完全 + 不 + 執行.
in between them. Very strange. Basically, the job appears to be unceremoniously dumped and anything left to execute in the job is +not+ executed at all.
但是，如果我刪除exec StartJob"(或者當我知道目標作業無法運行時，它只被調用一次)，一切正常，我在作業中的所有處理都會運行.
However, if I remove the "exec StartJob" (or have it invoked exactly one time, when I KNOW that the target job can't already be running), everything works perfectly and all my processing in the job runs through.
這一切背后的目的是讓作業作為觸發器的結果(除其他外)啟動，如果作業已經啟動，則真的沒有必要重新啟動".
The purpose behind all this is to have a job started as a result of a trigger (among other things), and, if the job is already started, there's really no need to "start it again".
有人在使用 SQL Agent 的作業處理時遇到過這樣的行為嗎?
Anyone ever run into behavior like this with SQL Agent's Job handling?
目前的控制流程是這樣的:
Current flow of control is like so:
- 更改為表(更新或插入)...
- 觸發調用...的觸發器
- 一個調用...的存儲過程
- sp_Start_Job 其中...
- 開始一個特定的工作......
- 調用另一個存儲過程(稱為 CheckQueue)...
- 執行一些處理并...
- 檢查幾個表格，根據它們的內容可能...
- 在另一個作業上調用 sp_start_job 以啟動第二個同步作業處理額外的工作(第二個作業也調用 CheckQueue sproc但是這兩個調用對完全獨立的數據集進行操作)
推薦答案
首先，你有沒有機會看一下 service broker?從你的描述來看，這似乎是你真正想要的.
First of all, have you had a chance to look at service broker? From your description, it sounds like that's what you actually want.
不同之處在于您將數據放入 SB 隊列而不是開始工作，SB 將異步調用您的處理過程，并且完全回避已經運行的工作等問題.它會自動生成/終止其他線程和需求決定，它負責秩序等.
The difference would be instead of starting a job, you put your data into a SB queue and SB will call your processing proc asynchronously and completely side-step issues with already-running jobs etc. It will auto spawn/terminate additional threads and demand dictates, it takes care of order etc.
這是一個很好的(并且相關的)教程.http://www.sqlteam.com/article/centralized-asynchronous-審計與服務經紀人
Here's a good (and vaguely related) tutorial. http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker
讓我們假設無論出于何種原因您都不能使用 SB(但說真的，一定要這樣做！).
Let's assume that you can't use SB for whatever reason (but seriously, do!).
如何使用作業 spid 的 context_info.
What about using the job spid's context_info.
- 您的工作調用了一個單獨執行每個步驟的包裝程序.
包裝過程中的第一條語句是
- Your job calls a wrapper proc that execs each step individually.
The first statement inside the wrapper proc is
DECLARE @context_info VARBINARY(30)
SET @context_info = CAST('MyJob1' AS VARBINARY)
SET CONTEXT_INFO @context_info
當您的 proc 完成時(或在您的 catch 塊中)
When your proc finishes (or in your catch block)
SET CONTEXT_INFO 0x0
當你打算打電話給你的工作時，這樣做:
When you are looking at calling your job, do this:
IF NOT EXISTS (SELECT * FROM master..sysprocesses WITH (NOLOCK) WHERE context_info=CAST('MyJob1' AS VARBINARY))
EXEC StartJob
當您的包裝過程終止或連接關閉時，您的 context_info 將消失.
When your wrapper proc terminates or the connection is closed, your context_info goes away.
您還可以使用全局臨時表(即##JobStatus)，當所有引用它的 spid 斷開連接或顯式刪除它時，它們將消失.
You could also use a global temp table (i.e. ##JobStatus) They will disappear when all spids that reference it disconnect or if it's explicitly dropped.
只是一些想法.
這篇關于如何準確檢測 SQL Server 作業是否正在運行并處理已運行的作業?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！