如何知道当前正在运行的作业的状态

我需要知道一个给定的作业是否正在 MsSQL2008服务器上运行。这样就不会再次调用可能导致并发问题的相同作业。

290091 次浏览
小开

可以查询表 msdb.dbo.sysjobactivity 来确定作业是否当前正在运行。

小开
最佳答案

看起来您可以使用 msdb.dbo.sysjobactivity，检查带有非 null start _ execute _ date 和 null stop _ execute _ date 的记录，这意味着作业已经启动，但尚未完成。

这将给你当前正在运行的作业:

SELECT sj.name
, sja.*
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
AND sja.stop_execution_date IS NULL
小开

给定一个工作(我假设你知道它的名字) ，你可以使用:

EXEC msdb.dbo.sp_help_job @Job_name = 'Your Job Name'

如 作业帮助程序所建议。 它返回大量关于作业的信息(所有者、服务器、状态等)。

小开 
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''

请注意“运行”列，显然1表示它当前正在运行，并且[ Current Step ]。 这会将 job _ id 返回给您，因此您需要查找它们，例如:

SELECT top 100 *
FROM   msdb..sysjobs
WHERE  job_id IN (0x9DAD1B38EB345D449EAFA5C5BFDC0E45, 0xC00A0A67D109B14897DD3DFD25A50B80, 0xC92C66C66E391345AE7E731BFA68C668)
小开 
EXEC msdb.dbo.sp_help_job @Job_name = 'Your Job Name'

检查字段 Execute _ status 执行状态

0-只返回那些没有空闲或挂起的作业。
1-执行。
2-等待线程。
3-重试之间。
4-闲置。
5-停赛。
7-执行完成动作。

如果需要执行结果，请检查字段 Last _ run _ result 最后运行结果

0 = 失败
1 = 成功
3 = 取消
5 = 未知

Https://msdn.microsoft.com/en-us/library/ms186722.aspx

小开

我找到了一个 肯尼斯 · 费舍尔的回答更好。以下查询只返回当前正在运行的作业:

SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE
ja.session_id = (
SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC
)
AND start_execution_date is not null
AND stop_execution_date is null;

通过在 select 子句中添加来自 msdb.dbo.sysjobactivity表的更多列，可以获得有关作业的更多信息。

小开 
DECLARE @StepCount INT
SELECT @StepCount = COUNT(1)
FROM msdb.dbo.sysjobsteps
WHERE job_id = '0523333-5C24-1526-8391-AA84749345666' --JobID




SELECT
[JobName]
,[JobStepID]
,[JobStepName]
,[JobStepStatus]
,[RunDateTime]
,[RunDuration]
FROM
(
SELECT
j.[name] AS [JobName]
,Jh.[step_id] AS [JobStepID]
,jh.[step_name] AS [JobStepName]
,CASE
WHEN jh.[run_status] = 0 THEN 'Failed'
WHEN jh.[run_status] = 1 THEN 'Succeeded'
WHEN jh.[run_status] = 2 THEN 'Retry (step only)'
WHEN jh.[run_status] = 3 THEN 'Canceled'
WHEN jh.[run_status] = 4 THEN 'In-progress message'
WHEN jh.[run_status] = 5 THEN 'Unknown'
ELSE 'N/A'
END AS [JobStepStatus]
,msdb.dbo.agent_datetime(run_date, run_time) AS [RunDateTime]
,CAST(jh.[run_duration]/10000 AS VARCHAR)  + ':' + CAST(jh.[run_duration]/100%100 AS VARCHAR) + ':' + CAST(jh.[run_duration]%100 AS VARCHAR) AS [RunDuration]
,ROW_NUMBER() OVER
(
PARTITION BY jh.[run_date]
ORDER BY jh.[run_date] DESC, jh.[run_time] DESC
) AS [RowNumber]
FROM
msdb.[dbo].[sysjobhistory] jh
INNER JOIN msdb.[dbo].[sysjobs] j
ON jh.[job_id] = j.[job_id]
WHERE
j.[name] = 'ProcessCubes' --Job Name
AND jh.[step_id] > 0
AND CAST(RTRIM(run_date) AS DATE) = CAST(GETDATE() AS DATE) --Current Date
) A
WHERE
[RowNumber] <= @StepCount
AND [JobStepStatus] = 'Failed'
小开

我们已经发现并且一直在使用这个代码来获得一个好的解决方案。这段代码将启动一个作业，并对其进行监视，如果作业超过时间限制，则自动终止该作业。

/****************************************************************
--This SQL will take a list of SQL Agent jobs (names must match),
--start them so they're all running together, and then
--monitor them, not quitting until all jobs have completed.
--
--In essence, it's an SQL "watchdog" loop to start and monitor SQL Agent Jobs
--
--Code from http://cc.davelozinski.com/code/sql-watchdog-loop-start-monitor-sql-agent-jobs
--
****************************************************************/
SET NOCOUNT ON


-------- BEGIN ITEMS THAT NEED TO BE CONFIGURED --------


--The amount of time to wait before checking again
--to see if the jobs are still running.
--Should be in hh:mm:ss format.
DECLARE @WaitDelay VARCHAR(8) = '00:00:20'


--Job timeout. Eg, if the jobs are running longer than this, kill them.
DECLARE @TimeoutMinutes INT = 240


DECLARE @JobsToRunTable TABLE
(
JobName NVARCHAR(128) NOT NULL,
JobID UNIQUEIDENTIFIER NULL,
Running INT NULL
)


--Insert the names of the SQL jobs here. Last two values should always be NULL at this point.
--Names need to match exactly, so best to copy/paste from the SQL Server Agent job name.
INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES ('NameOfFirstSQLAgentJobToRun',NULL,NULL)
INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES ('NameOfSecondSQLAgentJobToRun',NULL,NULL)
INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES ('NameOfXSQLAgentJobToRun',NULL,NULL)


-------- NOTHING FROM HERE DOWN SHOULD NEED TO BE CONFIGURED --------


DECLARE @ExecutionStatusTable TABLE
(
JobID UNIQUEIDENTIFIER PRIMARY KEY, -- Job ID which will be a guid
LastRunDate INT, LastRunTime INT, -- Last run date and time
NextRunDate INT, NextRunTime INT, -- Next run date and time
NextRunScheduleID INT, -- an internal schedule id
RequestedToRun INT, RequestSource INT, RequestSourceID VARCHAR(128),
Running INT,    -- 0 or 1, 1 means the job is executing
CurrentStep INT, -- which step is running
CurrentRetryAttempt INT, -- retry attempt
JobState INT -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread,
-- 3 = Between Retries, 4 = Idle, 5 = Suspended,
-- 6 = WaitingForStepToFinish, 7 = PerformingCompletionActions
)


DECLARE @JobNameToRun NVARCHAR(128) = NULL
DECLARE @IsJobRunning BIT = 1
DECLARE @AreJobsRunning BIT = 1
DECLARE @job_owner sysname = SUSER_SNAME()
DECLARE @JobID UNIQUEIDENTIFIER = null
DECLARE @StartDateTime DATETIME = GETDATE()
DECLARE @CurrentDateTime DATETIME = null
DECLARE @ExecutionStatus INT = 0
DECLARE @MaxTimeExceeded BIT = 0


--Loop through and start every job
DECLARE dbCursor CURSOR FOR SELECT JobName FROM @JobsToRunTable
OPEN dbCursor FETCH NEXT FROM dbCursor INTO @JobNameToRun
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [msdb].[dbo].sp_start_job @JobNameToRun
FETCH NEXT FROM dbCursor INTO @JobNameToRun
END
CLOSE dbCursor
DEALLOCATE dbCursor


print '*****************************************************************'
print 'Jobs started. ' + CAST(@StartDateTime as varchar)
print '*****************************************************************'


--Debug (if needed)
--SELECT * FROM @JobsToRunTable


WHILE 1=1 AND @AreJobsRunning = 1
BEGIN


--This has to be first with the delay to make sure the jobs
--have time to actually start up and are recognized as 'running'
WAITFOR DELAY @WaitDelay


--Reset for each loop iteration
SET @AreJobsRunning = 0


--Get the currently executing jobs by our user name
INSERT INTO @ExecutionStatusTable
EXECUTE [master].[dbo].xp_sqlagent_enum_jobs 1, @job_owner


--Debug (if needed)
--SELECT 'ExecutionStatusTable', * FROM @ExecutionStatusTable


--select every job to see if it's running
DECLARE dbCursor CURSOR FOR
SELECT x.[Running], x.[JobID], sj.name
FROM @ExecutionStatusTable x
INNER JOIN [msdb].[dbo].sysjobs sj ON sj.job_id = x.JobID
INNER JOIN @JobsToRunTable jtr on sj.name = jtr.JobName
OPEN dbCursor FETCH NEXT FROM dbCursor INTO @IsJobRunning, @JobID, @JobNameToRun


--Debug (if needed)
--SELECT x.[Running], x.[JobID], sj.name
--  FROM @ExecutionStatusTable x
--  INNER JOIN msdb.dbo.sysjobs sj ON sj.job_id = x.JobID
--  INNER JOIN @JobsToRunTable jtr on sj.name = jtr.JobName


WHILE @@FETCH_STATUS = 0
BEGIN
--bitwise operation to see if the loop should continue
SET @AreJobsRunning = @AreJobsRunning | @IsJobRunning


UPDATE @JobsToRunTable
SET Running = @IsJobRunning, JobID = @JobID
WHERE JobName = @JobNameToRun


--Debug (if needed)
--SELECT 'JobsToRun', * FROM @JobsToRunTable


SET @CurrentDateTime=GETDATE()


IF @IsJobRunning = 1
BEGIN -- Job is running or finishing (not idle)


IF DATEDIFF(mi, @StartDateTime, @CurrentDateTime) > @TimeoutMinutes
BEGIN
print '*****************************************************************'
print @JobNameToRun + ' exceeded timeout limit of ' + @TimeoutMinutes + ' minutes. Stopping.'
--Stop the job
EXEC [msdb].[dbo].sp_stop_job @job_name = @JobNameToRun
END
ELSE
BEGIN
print @JobNameToRun + ' running for ' + CONVERT(VARCHAR(25),DATEDIFF(mi, @StartDateTime, @CurrentDateTime)) + ' minute(s).'
END
END


IF @IsJobRunning = 0
BEGIN
--Job isn't running
print '*****************************************************************'
print @JobNameToRun + ' completed or did not run. ' + CAST(@CurrentDateTime as VARCHAR)
END


FETCH NEXT FROM dbCursor INTO @IsJobRunning, @JobID, @JobNameToRun


END -- WHILE @@FETCH_STATUS = 0
CLOSE dbCursor
DEALLOCATE dbCursor


--Clear out the table for the next loop iteration
DELETE FROM @ExecutionStatusTable


print '*****************************************************************'


END -- WHILE 1=1 AND @AreJobsRunning = 1


SET @CurrentDateTime = GETDATE()
print 'Finished at ' + CAST(@CurrentDateTime as varchar)
print CONVERT(VARCHAR(25),DATEDIFF(mi, @StartDateTime, @CurrentDateTime)) + ' minutes total run time.'
小开

这个查询将为您提供当前正在运行的作业的确切输出。这还将显示运行作业的持续时间(以分钟为单位)。

   WITH
CTE_Sysession (AgentStartDate)
AS
(
SELECT MAX(AGENT_START_DATE) AS AgentStartDate FROM MSDB.DBO.SYSSESSIONS
)
SELECT sjob.name AS JobName
,CASE
WHEN SJOB.enabled = 1 THEN 'Enabled'
WHEN sjob.enabled = 0 THEN 'Disabled'
END AS JobEnabled
,sjob.description AS JobDescription
,CASE
WHEN ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NULL  THEN 'Running'
WHEN ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NOT NULL AND HIST.run_status = 1 THEN 'Stopped'
WHEN HIST.run_status = 0 THEN 'Failed'
WHEN HIST.run_status = 3 THEN 'Canceled'
END AS JobActivity
,DATEDIFF(MINUTE,act.start_execution_date, GETDATE()) DurationMin
,hist.run_date AS JobRunDate
,run_DURATION/10000 AS Hours
,(run_DURATION%10000)/100 AS Minutes
,(run_DURATION%10000)%100 AS Seconds
,hist.run_time AS JobRunTime
,hist.run_duration AS JobRunDuration
,'tulsql11\dba' AS JobServer
,act.start_execution_date AS JobStartDate
,act.last_executed_step_id AS JobLastExecutedStep
,act.last_executed_step_date AS JobExecutedStepDate
,act.stop_execution_date AS JobStopDate
,act.next_scheduled_run_date AS JobNextRunDate
,sjob.date_created AS JobCreated
,sjob.date_modified AS JobModified
FROM MSDB.DBO.syssessions AS SYS1
INNER JOIN CTE_Sysession AS SYS2 ON SYS2.AgentStartDate = SYS1.agent_start_date
JOIN  msdb.dbo.sysjobactivity act ON act.session_id = SYS1.session_id
JOIN msdb.dbo.sysjobs sjob ON sjob.job_id = act.job_id
LEFT JOIN  msdb.dbo.sysjobhistory hist ON hist.job_id = act.job_id AND hist.instance_id = act.job_history_id
WHERE ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NULL
ORDER BY ACT.start_execution_date DESC

提交答案