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
Jared Zagelbaum- The Blog 
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
)
LikeLiked by 1 person
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.
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike