Script to show SQL Server agent jobs that automatically delete

Problem: There you are on a Monday morning being a good DBA and checking into your SQL agent estate and verifying that jobs ran as expected. You check your e-mail inbox and notice the alert that a particular job failed, so what do you do?
Most of us will open SSMS (SQL Server Management Studio), connect to the offending instance and open Job Activity Monitor, look for the job, right-click and view the history. But wait, what’s this? The job doesn’t exist?! And furthermore, there is no history within the log file viewer either?! Has this happened to you? What is going on?

This could very well be a job configured with an auto-deletion notification. But don’t be fooled. This “setting” is not a notification, but instead a job action, an instruction on what to do with the job when the SQL agent engine is finished executing it.

Within the setup of a SQL Server agent job, under the Notifications tab, there is an action checkbox to Automatically delete job with a dropdown having the following options:

• When the job succeeds
• When the job fails
• When the job completes

This “feature” does exactly what the option describes. Upon succeeding, failing or whenever the job completes (regardless of the outcome), the job will be deleted. Why you would want this and the history purged is baffling to me, and I do not understand why this would even be an option. Nevertheless, it exists, and it could be dangerous, especially if you are troubleshooting and need the job history as a reference.

Solution: Below is my script to check for these types of jobs before they potentially become a problem.

select
case
	when delete_level = 0 then 'Never'
	when delete_level = 1 then 'When the job succeeds'
	when delete_level = 2 then 'When the job fails'
	when delete_level = 3 then 'Whenever the job completes'
end as [Delete Job], delete_level, *
from msdb.dbo.sysjobs
where delete_level <> 0
order by [name]

A screen capture below, shows the script and an example of the output. In the case, three jobs each with a different delete level (action) configured.

As mentioned the only indication that you as the DBA will have is by reviewing the SQL Server Agent logs. Something like the following will be shown:

Even the output in the SQL agent log is not very good. In my example, the job was deleted because of it having a delete level of 1. Well, if you don’t deal with this scenario all too often, over to Microsoft Docs you go looking up delete level and the possible values of that lookup table. I will save you the time; this msdb.dbo.sysjobs article has the information detailed. It states the following at the time of this writing:

delete_level

int

Bitmask indicating under what circumstances the job should be deleted when a job completes: 0 = Never 1 = When the job succeeds 2 = When the job fails 3 = Whenever the job completes (regardless of the job outcome)

So, knowing this, in my example we can see the job completed successfully since the delete level recorded in the log was 1. However, if the job fails, the only indication would be (job has delete level 2) and there isn’t a means of referencing the msdb.dbo.sysjobhistory system table because the job history is turfed when the job is deleted.

This is where I have a problem with this type of action on an agent job, I want the history, and more importantly, I want the code behind the job for reference in the future. I don’t understand why Microsoft has this logic. Personally I would want the job to be automatically disabled when any of those actions have been met so it won’t potentially run again.

This also emphasizes the need for scripting out your jobs if they are not contained within any type of source code repository. You could restore a copy of the msdb database, and crawl through the msdb.dbo.sysjobsteps system table and try to piece together the job or at least see the code behind it, but who has the time or patience to do that? If the job was that important, I’m sure you have it scripted out somewhere…right?!

If you have a valid reason to use this “feature” within a SQL agent job, I’d love to hear about it in the comments below. Let me know.

---

Did you find this useful? Did it help you solve an immediate need within your SQL Server agent estate? If so, you might want to check out SQL Agent Insight, a product I’ve been working on during spare time to assist in determining problem areas of SQL Server agent instances.

---