87 lines
3.0 KiB
Transact-SQL
87 lines
3.0 KiB
Transact-SQL
SET NOCOUNT ON;
|
|
|
|
/*=============================================================================
|
|
|
|
Last last job status. Can use a filtered job list or all the jobs.
|
|
derived from https://sqlconjuror.com/sql-server-t-sql-script-to-check-job-run-status/
|
|
|
|
|
|
Context
|
|
----------------------
|
|
anywhere
|
|
|
|
Creation : 30.12.2022 / TSC
|
|
Modifications:
|
|
|
|
=============================================================================*/
|
|
|
|
--If @allJobs is set to 0, then only a subset of jobs are checked (see insert into @jobs)
|
|
DECLARE @allJobs BIT = 01;
|
|
DECLARE @jobs TABLE (
|
|
[name] sysname NOT NULL
|
|
,[job_id] UNIQUEIDENTIFIER NOT NULL
|
|
,[failed] BIT NOT NULL
|
|
DEFAULT 0
|
|
,[lastRun] DATETIME NULL
|
|
);
|
|
|
|
IF @allJobs = 0
|
|
BEGIN
|
|
INSERT INTO @jobs ([name], [job_id])
|
|
SELECT
|
|
[s].[name]
|
|
,[s].[job_id]
|
|
FROM [msdb].[dbo].[sysjobs] [s]
|
|
WHERE [s].[name] IN (
|
|
'zz_tsc OCTPDBA-440 supra'
|
|
,'__D70010 - After APSSynchroLoad'
|
|
,'_D73031 - SYNC - H Synchronize items and addresses - Central'
|
|
,'_D73061 - ITEM - 000 - Manage data before H synchronization - Central'
|
|
,'_D73071 - ITEM - Manage data after H synchronization - Central'
|
|
,'_D73110 - SYNC - V Synchronization extraction - Central'
|
|
,'D73130 - VESTA - SYNC - Interbase 2.0'
|
|
,'__D03011 - After working hours - Central'
|
|
);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO @jobs ([name], [job_id])
|
|
SELECT
|
|
[s].[name]
|
|
,[s].[job_id]
|
|
FROM [msdb].[dbo].[sysjobs] [s]
|
|
END
|
|
|
|
SELECT
|
|
CONVERT(VARCHAR(20), SERVERPROPERTY('ServerName')) AS [ServerName]
|
|
,[j].[name] AS [job_name]
|
|
,CASE [j].[enabled]
|
|
WHEN 1 THEN 'Enabled'
|
|
ELSE 'Disabled'
|
|
END AS [job_status]
|
|
,CASE [jh].[run_status]
|
|
WHEN 0 THEN 'Error Failed'
|
|
WHEN 1 THEN 'Succeeded'
|
|
WHEN 2 THEN 'Retry'
|
|
WHEN 3 THEN 'Cancelled'
|
|
WHEN 4 THEN 'In Progress'
|
|
ELSE 'Status Unknown'
|
|
END AS [last_run_status]
|
|
,[ja].[run_requested_date] AS [last_run_date]
|
|
,CONVERT(
|
|
VARCHAR(10)
|
|
,CONVERT(DATETIME, RTRIM(19000101))
|
|
+ ([jh].[run_duration] * 9 + [jh].[run_duration] % 10000 * 6 + [jh].[run_duration] % 100 * 10) / 216e4
|
|
,108
|
|
) AS [run_duration]
|
|
,[ja].[next_scheduled_run_date]
|
|
,CONVERT(VARCHAR(500), [jh].[message]) AS [step_description]
|
|
FROM([msdb].[dbo].[sysjobactivity] [ja]
|
|
JOIN @jobs [j2] ON j2.[job_id] = ja.[job_id]
|
|
LEFT JOIN [msdb].[dbo].[sysjobhistory] [jh]
|
|
ON [ja].[job_history_id] = [jh].[instance_id])
|
|
JOIN [msdb].[dbo].[sysjobs_view] [j]
|
|
ON [ja].[job_id] = [j].[job_id]
|
|
WHERE [ja].[session_id] = (SELECT MAX([session_id])FROM [msdb].[dbo].[sysjobactivity])
|
|
ORDER BY [job_status] DESC,[last_run_status];
|