Sprawdzenie aktualnie działających jobów SQL Agent
Wykonujemy w SSMS z wybraną bazą master
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
,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 DESCPrzykładowy wynik:
