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.