Query to Find missing job in SQL Server
While managing multiple instance of SQL Server, it is little bit difficult to keep watch on every jobs running in every SQL Server Instances we are managing. I have created one monitoring application which suppose to be watched by at least one DBA 24*7. I have one section of “Missing Job” in my monitoring application so that whenever any job failed the schedule, one of the DBA get an alert and s/he can do needful.
Following is the TSQL script which I use to find missing job.
SET nocount ON
DECLARE @datetime VARCHAR(12)
SET @datetime = CONVERT(VARCHAR, Getdate(), 112)
CREATE TABLE #runningjobs
(
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id SYSNAME COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)
INSERT INTO #runningjobs
EXECUTE master.dbo.Xp_sqlagent_enum_jobs
1,
'sa';
WITH cte
AS (SELECT [sJOB].[job_id] AS [JobID],
[sJOB].[name] AS [JobName],
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 [LastRunDateTime],
CASE [sJOBH].[run_status]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'Running'
END AS [LastRunStatus],
Stuff(Stuff(RIGHT('000000'
+ Cast([sJOBH].[run_duration] AS VARCHAR(6)),6), 3, 0, ':'),
6, 0, ':') AS [LastRunDuration (HH:MM:SS)],
[sJOBH].[message] AS [LastRunStatusMessage],
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 [NextRunDateTime],
sJob.enabled
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)
SELECT 'Job Missing (' + @@SERVERNAME + ')' AS CounterName,
Count(*) AS Value
FROM cte
WHERE nextrundatetime >= Dateadd(dd, 0, Datediff(dd, 0, Getdate()))
AND nextrundatetime < Dateadd(minute, -30, Getdate())
AND ( nextrundatetime > lastrundatetime
OR lastrundatetime IS NULL )
AND enabled = 1
AND jobid NOT IN (SELECT job_id
FROM #runningjobs
WHERE running = 1)
DROP TABLE #runningjobs
GO
BTW, I have written few more articles related to SQL Server Job. If you are interested, have a look at following list:
- Get list of SQL Server Jobs from multiple instance via SSIS package (Click Here)
- Find SQL Server Agent job ran on specific date with its status (Click Here)
- Search SQL Server Job based on keyword provided (Click Here)
- Script all SQL Server Job automatically by SSIS and SMO (Click Here)
- Get list of failed SQL Server Agent job in SQL Server (Click Here)
If you like this article, do like “Extreme-Advice” page in Facebook.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.