Today I want to share a useful script which will help us to clutter list of jobs that either have not run in X months or where the next schedule run date is older than today.
Just run this against any server. Investigate if the job can be deleted.
SELECT @@SERVERNAME SvrName, J.Name, J.[Enabled], JA.LastRun, JA.NextRun, JV.[description], JC.Name JobCategory
FROM msdb.dbo.sysjobs J
JOIN msdb.dbo.sysjobs_view JV
ON J.Job_ID = JV.Job_ID
SELECT Job_ID, MAX(Last_Executed_Step_Date) LastRun, MAX(Next_Scheduled_Run_Date) NextRun
GROUP BY Job_Id
ON J.Job_ID = JA.Job_ID
JOIN msdb.dbo.syscategories JC
ON J.Category_ID = JC.category_id
WHERE DATEDIFF(m, ISNULL(LastRun, ‘1900-01-01’), GETDATE()) > 12
OR NextRun < GETDATE()
I would like to have feedback from my blog readers.
Please post your feedback, question, or comments about this article.