Delete unused SQL Agent jobs from instance

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
Advertisements

5 thoughts on “Delete unused SQL Agent jobs from instance

  1. what about jobs that are enabled, but with a disabled schedule ? If you want to remove them too, the original query needs to be something like this

    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 js.job_id
    from msdb.dbo.sysjobschedules js
    inner join msdb.dbo.sysschedules sc
    on sc.schedule_id = js.schedule_id
    where sc.enabled = 1
    )

    Liked by 1 person

  2. Actually you are finding jobs that are not in Job History which is not quite the same as jobs never run. Generally the job history is cleaned up and somewhat limited although these settings are configurable. The defaults are on the order of 1000 rows of Job History (Max of 100 per job) or perhaps 4 weeks of history.

    I think it would be prudent to script the jobs prior to deleting them and save the scripts in a repository somewhere.

    Like

    • Absolutely Ray. As I mentioned in the post, a more conservative approach would be to use sp_update_job and simply set the enabled property instead of deleting the job. For the specific case I was working on, the client never purged job history, so it wasn’t an issue.

      Like

  3. I like to code my not exists explicitly–that left join approach takes me a minute to recognize. I had a similar task for a client and this is what I came up with to identify jobs not in the history.

    SELECT
    sj.name as jobName,
    sj.[enabled]

    FROM dbo.sysjobs sj

    where not exists (select * from dbo.sysjobhistory jh
    where jh.job_id = sj.job_id)

    order by sj.name

    Like

  4. Oh, I just remembered, sql agent keeps a specified number of job history steps for all jobs–I think the default is 1000–steps, not jobs. I had a client where we increased that because they were losing history. So, that may be something to keep in mind.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s