sqlserver 代理作业监控

SELECT [sJOB].[job_id] AS [作业ID] ,

[sJOB].[name] AS job ,

CASE WHEN [sJOBH].[run_date] IS NULL

OR [sJOBH].[run_time] IS NULL THEN NULL

ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8)) + ' '

+ STUFF(STUFF(RIGHT('000000'

+ CAST([sJOBH].[run_time] AS VARCHAR(6)),

6), 3, 0, ':'), 6, 0, ':') AS DATETIME)

END AS [最近执行时间] ,

CASE [sJOBH].[run_status]

WHEN 0 THEN 'fail'

WHEN 1 THEN 'succ'

WHEN 2 THEN 'retry'

WHEN 3 THEN 'cancel'

WHEN 4 THEN 'run' -- In Progress

END AS status ,

STUFF(STUFF(RIGHT('000000'

+ CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6), 3,

0, ':'), 6, 0, ':') AS LastRunDuration ,

[sJOBH].[message] AS [最近运行状态信息] ,

CASE [sJOBSCH].[NextRunDate]

WHEN 0 THEN NULL

ELSE CAST(CAST([sJOBSCH].[NextRunDate] AS CHAR(8)) + ' '

+ STUFF(STUFF(RIGHT('000000'

+ CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),

6), 3, 0, ':'), 6, 0, ':') AS DATETIME)

END AS [下次运行时间] INTO #tmp

FROM [msdb].[dbo].[sysjobs] AS [sJOB]

LEFT JOIN ( SELECT [job_id] ,

MIN([next_run_date]) AS [NextRunDate] ,

MIN([next_run_time]) AS [NextRunTime]

FROM [msdb].[dbo].[sysjobschedules]

GROUP BY [job_id]

) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]

LEFT JOIN ( SELECT [job_id] ,

[run_date] ,

[run_time] ,

[run_status] ,

[run_duration] ,

[message] ,

ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber

FROM [msdb].[dbo].[sysjobhistory]

WHERE [step_id] = 0

) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id]

AND [sJOBH].[RowNumber] = 1

ORDER BY job

SELECT * FROM #tmp

DECLARE @url nVARCHAR(1024),@job nVARCHAR(256),@info nVARCHAR(128),@LastRunDuration nVARCHAR(32)

--

SELECT @url = ''

DECLARE cur CURSOR FOR SELECT job,LastRunDuration FROM #tmp WHERE status='succ'

OPEN cur

FETCH NEXT FROM cur INTO @job,@LastRunDuration

WHILE @@FETCH_STATUS=0

BEGIN

SET @info=''

SET @info=@job+N' 执行失败，最近执行时间：'+@LastRunDuration

EXEC msdb.dbo.sp_start_job @job_name=@job

SET @info=@url+@info

SELECT @info

EXEC DBMonitor.dbo.P_POST_HttpRequestData @URL = @info,

@DATA = '',

@REQ_H_ACCEPT = 'application/json',

@REQ_H_CONTENT_TYPE = 'application/json';

FETCH NEXT FROM cur INTO @job,@LastRunDuration

END

CLOSE cur

DEALLOCATE cur

DROP TABLE #tmp