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()

Snapshot of the outcome below.

Tagged: Jobs, SQL Server