Статус заданий SQL Server Agent (Jobs)

Сегодня я бы хотел поговорить о такой не распространённой теме, как статус задания SQL Server Agent, хотя это бывает очень необходимо. Есть несколько способов узнать статус текущий заданий SQL Server Agent:

Способ первый:

Самый очевидный и простой способ узнать статус заданий SQL Server Agent это запустить Job Activity Monitor в SQL Server Management Studio, который находится в закладке SQL Server Agent:

Двойной клик на Job Activity Monitor откроет новое окно со статусом всех заданий, возможна сортировка по нужной вам колонке.

Способ второй:

Использование оснастки очень удобно, но бывает, что нам необходимо получить ту же самую информацию, а возможно и больше, через скрипты. Здесь так же есть несколько вариантов.

Использование процедуры sp_help_job:

exec msdb.dbo.sp_help_job @execution_status=1

Так же можно использовать недокументированную процедуру xp_sqlagent_enum_jobs. Возможно вам потребуется включить Agent XP в конфигурации сервера (sp_configure).

Следующий запрос вернёт информацию о запущенных заданиях. Всегда будьте аккуратны при использовании недокументированных процедур:

IF OBJECT_ID('tempdb.dbo.#RunningJobs') IS NOT NULL
DROP TABLE #RunningJobs
CREATE TABLE #RunningJobs (
Job_ID UNIQUEIDENTIFIER,
Last_Run_Date INT,
Last_Run_Time INT,
Next_Run_Date INT,
Next_Run_Time INT,
Next_Run_Schedule_ID INT,
Requested_To_Run INT,
Request_Source INT,
Request_Source_ID VARCHAR(100),
Running INT,
Current_Step INT,
Current_Retry_Attempt INT,
State INT )INSERT INTO #RunningJobs EXEC master.dbo.xp_sqlagent_enum_jobs 1,garbage
SELECT
name AS [Job Name]
,CASE WHEN next_run_date=0 THEN '[Not scheduled]' ELSE
CONVERT(VARCHAR,DATEADD(S,(next_run_time/10000)*60*60 /* hours */
+((next_run_time - (next_run_time/10000) * 10000)/100) * 60 /* mins */
+ (next_run_time - (next_run_time/100) * 100)  /* secs */,
CONVERT(DATETIME,RTRIM(next_run_date),112)),100) END AS [Start Time]
FROM     #RunningJobs JSR
JOIN     msdb.dbo.sysjobs
ON       JSR.Job_ID=sysjobs.job_id
WHERE    Running=1 -- i.e. still running
ORDER BY name,next_run_date,next_run_time

История выполнения заданий

Для получения истории всех запусков заданий за последние 24 часа, можете воспользоваться следующим запросом. Скрипт вернёт статус по каждому запуску:

USE MSDB
SELECT name AS [Job Name]
,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100)  /* secs */
,CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]
,CASE WHEN enabled=1 THEN 'Enabled'
ELSE 'Disabled'
END [Job Status]
,CASE WHEN SJH.run_status=0 THEN 'Failed'
WHEN SJH.run_status=1 THEN 'Succeeded'
WHEN SJH.run_status=2 THEN 'Retry'
WHEN SJH.run_status=3 THEN 'Cancelled'
ELSE 'Unknown'
END [Job Outcome]
FROM   sysjobhistory SJH
JOIN   sysjobs SJ
ON     SJH.job_id=sj.job_id
WHERE  step_id=0
AND    DATEADD(S,
(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100)  /* secs */,
CONVERT(DATETIME,RTRIM(run_date),113)) >= DATEADD(d,-1,GetDate())
ORDER BY name,run_date,run_time

Для получения информации о конкретном задании, вы можете воспользоваться следующей процедурой (данную процедуру использует компонент Job Activity Monitor)

exec msdb.dbo.sp_help_job @job_name = 'Job_Name'

Список проблемных заданий

Предыдущий скрипт может возвращать множество строк, что осложнит анализ. Следующий скрипт более наглядный, но выводит всего по 1 строке для каждого задания. В результате вы получите список заданий, которые завершились с ошибками за последнее время. Данный скрипт позволит быстро понять куда необходимо смотреть:

;WITH CTE_MostRecentJobRun AS
(
-- For each job get the most recent run (this will be the one where Rnk=1)
SELECT job_id,run_status,run_date,run_time
,RANK() OVER (PARTITION BY job_id ORDER BY run_date DESC,run_time DESC) AS Rnk
FROM sysjobhistory
WHERE step_id=0
)
SELECT
name  AS [Job Name]
,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100)  /* secs */,
CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]
,CASE WHEN enabled=1 THEN 'Enabled'
ELSE 'Disabled'
END [Job Status]
FROM     CTE_MostRecentJobRun MRJR
JOIN     sysjobs SJ
ON       MRJR.job_id=sj.job_id
WHERE    Rnk=1
AND      run_status=0 -- i.e. failed
ORDER BY name