I recently worked on a project where the client requested to “remove all of the unused jobs” that had been installed across their production instances over time. I originally thought that using the enabled property would lead me to the right jobs, but as that is user dependent, and the fact that users created the mess in the first place, that didn’t end up being the best way to thin out the herd.
A couple of more iterations led me to this solution, which looks for sql agent jobs that have never been run, and are not currently scheduled, then executes sp_delete_job against all applicable job_ids. A more conservative approach could be to modify the script to use sp_update_job and simply flip the enabled bit. As I mentioned before, the enabled property wasn’t being well maintained across the instances, and it was a short term engagement, so I opted for this more aggressive approach:
--Script to delete sql server agent jobs that have never been executed or scheduled --Authored by Jared Zagelbaum 4/2015 jaredzagelbaum.wordpress.com DECLARE @jobID UNIQUEIDENTIFIER --variable for job_id DECLARE jobidCursor CURSOR FOR --used for cursor allocation --jobs never run SELECT j.job_id FROM msdb.dbo.sysjobs j left outer join msdb.dbo.sysjobhistory jh on j.job_id = jh.job_id where jh.job_id is null --jobs not scheduled to run and j.job_id NOT IN ( select job_id from msdb.dbo.sysjobschedules ) -- deletion OPEN jobidCursor FETCH NEXT FROM jobidCursor INTO @jobID WHILE @@Fetch_Status = 0 BEGIN EXEC msdb.dbo.sp_delete_job @job_id=@jobID FETCH Next FROM jobidCursor INTO @jobID END CLOSE jobidCursor DEALLOCATE jobidCursor