I had to find all the jobs that either least run(one in 4 months) or never executed on all our SQL environments. i wrote the below script that pulls up that information.
SELECT Jb.Name AS [Job Name], CASE WHEN Jb.[Enabled] = 1 THEN ‘Yes’ ELSE ‘No’ END AS [Enabled], Jact.LastRun AS [Last Run Date], Jact.NextRun AS [Next Run Date]
FROM msdb.dbo.sysjobs Jb
JOIN msdb.dbo.sysjobs_view Jv
ON Jb.Job_ID = Jv.Job_ID
JOIN
(
SELECT Job_ID, MAX(Last_Executed_Step_Date) LastRun, MAX(Next_Scheduled_Run_Date) NextRun
FROM msdb.dbo.sysjobactivity
GROUP BY Job_Id
) Jact
ON Jb.Job_ID = Jact.Job_ID
JOIN msdb.dbo.syscategories JC
ON Jb.Category_ID = JC.category_id
WHERE DATEDIFF(day, ISNULL(LastRun, ‘1900-01-01’), GETDATE()) > 120
OR NextRun < GETDATE()
FROM msdb.dbo.sysjobs Jb
JOIN msdb.dbo.sysjobs_view Jv
ON Jb.Job_ID = Jv.Job_ID
JOIN
(
SELECT Job_ID, MAX(Last_Executed_Step_Date) LastRun, MAX(Next_Scheduled_Run_Date) NextRun
FROM msdb.dbo.sysjobactivity
GROUP BY Job_Id
) Jact
ON Jb.Job_ID = Jact.Job_ID
JOIN msdb.dbo.syscategories JC
ON Jb.Category_ID = JC.category_id
WHERE DATEDIFF(day, ISNULL(LastRun, ‘1900-01-01’), GETDATE()) > 120
OR NextRun < GETDATE()
Snapshot of the outcome below.
No comments:
Post a Comment