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