如何知道 SQL AGENT JOB 的状态

这个问题，需要查询两个：

MSDB：

SELECT j.job_id , j.name FROM msdb..sysjobs j WHERE EXISTS ( SELECT 1 FROM msdb..sysjobsteps sj INNER JOIN msdb..sysjobactivity ja ON ja.job_id = j.job_id WHERE sj.job_id = j.job_id AND ja.start_execution_date <= GETDATE() AND ja.stop_execution_date IS NULL AND ja.session_id = ( SELECT MAX(ja2.session_id) FROM msdb..sysjobactivity ja2 WHERE ja2.job_id = j.job_id ) )

另一个，SYE.DM_EXEC_SESSIONS:

SELECT j.job_id, j.name FROM msdb..sysjobs j WHERE dbo.uf_HexToChar(j.job_id, 16) IN ( SELECT SUBSTRING(sp.[program_name], 32, 32) FROM sys.dm_exec_sessions sp WHERE sp.status IN ( 'running', 'sleeping' )

上面有个自定义函数：

CREATE FUNCTION [dbo].[uf_HexToChar] ( @binaryValue VARBINARY(100) , @numBytes INT ) RETURNS VARCHAR(200) AS BEGIN DECLARE @i VARBINARY(10) , @hexdigits CHAR(16) , @s VARCHAR(100) , @h VARCHAR(100) , @currentByte SMALLINT SET @hexdigits = '0123456789ABCDEF' SET @currentByte = 0 SET @h = '' -- process all bytes WHILE @currentByte < @numBytes BEGIN SET @currentByte = @currentByte + 1 -- get first character of byte SET @i = SUBSTRING(CAST(@binaryValue AS VARBINARY(100)), @currentByte, 1) -- get the first character SET @s = CAST(SUBSTRING(@hexdigits, @i % 16 + 1, 1) AS CHAR(1)) -- shift over one character SET @i = @i / 16 -- get the second character SET @s = CAST(SUBSTRING(@hexdigits, @i % 16 + 1, 1) AS CHAR(1)) + @s -- build string of hex characters SET @h = @h + @s END RETURN(@h) END GO