在特定時間停止其他作業的作業
我們有一個每天晚上從遠端 SQL Server 導入數據的工作,我稱之為 Import-Job。大多數情況下,這項工作在大約 15 分鐘內完成,一切都很好,但由於 VPN 連接相當不穩定,有時這項工作需要更多時間。
我們為這項工作指定了 1.5 小時的時間視窗,我們不能冒險讓 Import-Job 超過這個視窗(其他工作和生產從某個時間點開始)。
我們在時間視窗結束時使用另一個執行以下 TSQL 語句的作業停止 Import-Job:
IF EXISTS ( SELECT 1 FROM msdb.dbo.sysjobs J JOIN msdb.dbo.sysjobactivity A ON A.job_id=J.job_id WHERE J.name=N'Import-Job' AND A.run_requested_date IS NOT NULL AND A.stop_execution_date IS NULL ) BEGIN EXEC msdb.dbo.sp_stop_job N'Import-Job'; PRINT 'The job was stopped.' END ELSE BEGIN PRINT 'The job is not running.' END
這確實有效,但它每次都會嘗試停止 Import-Job,即使 Import-Job 沒有執行,也會導致 Job History 中出現錯誤:
QLServerAgent Error: Request to stop job Import-Job (from User ServiceUser) refused because the job is not currently running.
我在這個語句中遺漏了什麼,我如何讓這個 T-SQL 腳本工作,這樣它就不會在 Import-Job 沒有執行時嘗試停止它?(我想擺脫錯誤)
如果你
SELECT
在你的 中執行IF EXISTS
,我想你會發現它至少返回一行(可能是幾行),即使你的工作沒有執行SELECT 1 FROM msdb.dbo.sysjobs J JOIN msdb.dbo.sysjobactivity A ON A.job_id=J.job_id WHERE J.name=N'Import-Job' AND A.run_requested_date IS NOT NULL AND A.stop_execution_date IS NULL
這樣做的原因是,如果 SQL 代理程序在作業執行時意外終止,它將永遠不會填充結束日期(這可能在作業執行時重新啟動伺服器時發生)。在您的程式碼中顯示舊作業
A.run_requested_date IS NOT NULL AND A.stop_execution_date IS NULL
應該是常見且預期的。許多人
xp_sqlagent_enum_jobs
用來獲取工作目前狀態,但是這是一個未記錄的擴展程序,所以我傾向於不使用它。xp_sqlagent_enum_jobs
我在這篇博文中進一步解釋了我不喜歡的原因。為了大致
xp_sqlagent_enum_jobs
完成使用 T-SQL 所做的事情,我使用了這樣的東西:SELECT TOP 1 IsRunning = CASE WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL THEN 1 ELSE 0 END, LastRunTime = ja.start_execution_date, NextRunTime = ja.next_scheduled_run_date, LastJobStep = js.step_name, JobOutcome = CASE WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL THEN 'Running' WHEN run_status = 0 THEN 'Failed' WHEN run_status = 1 THEN 'Succeeded' WHEN run_status = 2 THEN 'Retry' WHEN run_status = 3 THEN 'Cancelled' END FROM msdb.dbo.sysjobs j LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = j.job_id AND ja.run_requested_date IS NOT NULL AND ja.start_execution_date IS NOT NULL LEFT JOIN msdb.dbo.sysjobsteps js ON js.job_id = ja.job_id AND js.step_id = ja.last_executed_step_id LEFT JOIN msdb.dbo.sysjobhistory jh ON jh.job_id = j.job_id AND jh.instance_id = ja.job_history_id WHERE j.name = @JobName ORDER BY ja.start_execution_date DESC;
作為我的開源DBA 數據庫的一部分,這個
xp_agent_enum_jobs
替代方案在 GitHub 上作為一個函式提供。使用該函式,您的代理作業步驟將如下所示:
IF EXISTS ( SELECT 1 FROM msdb.dbo.sysjobs j CROSS APPLY dbo.AgentJob_Status (j.name) sts WHERE j.name = 'Import-Job' AND sts.IsRunning = 1 ) BEGIN EXEC msdb.dbo.sp_stop_job N'Import-Job'; PRINT 'The job was stopped.' END ELSE BEGIN PRINT 'The job is not running.' END
或者,如果您只想從函式中提取必要的程式碼並將其內聯到您的工作步驟中,您可以執行以下操作:請注意,主要的功能差異(解決上面提到的“舊行”問題)是添加
TOP 1...ORDER BY
一個SELECT
:IF EXISTS ( SELECT TOP 1 1 FROM msdb.dbo.sysjobs j LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = j.job_id AND ja.run_requested_date IS NOT NULL AND ja.start_execution_date IS NOT NULL LEFT JOIN msdb.dbo.sysjobsteps js ON js.job_id = ja.job_id AND js.step_id = ja.last_executed_step_id LEFT JOIN msdb.dbo.sysjobhistory jh ON jh.job_id = j.job_id AND jh.instance_id = ja.job_history_id WHERE j.name = 'Import-Job' AND ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL ORDER BY ja.start_execution_date DESC ) BEGIN EXEC msdb.dbo.sp_stop_job N'Import-Job'; PRINT 'The job was stopped.' END ELSE BEGIN PRINT 'The job is not running.' END