Have you ever looked at something in SQL Server and wonder why it is there? That is what I think when I see this option in the SQL Server Agent job properties. I can not come up with any good reason of why you would want a job to delete itself upon completion. I even did a Google search and really didn’t find a good reason. However, if you know of a great reason of why you would want to enable this, I would love to hear about it.
If you are not familiar with this option, you can find it under the Notifications tab of the job properties.
Just like all the notifications, you have several options.
- When the job secceeds
- When the job fails
- When the job compeletes
When the job deletes, it will also take a job history with it. Basically there will be no evidence that the job ever existed. This is pretty straight forward, so you might be wondering why bring it up. Well, I see this setting as a potential danger, especially if you have a disgruntled DBA. While being a DBA is a great job, there are, from time to time things that can have a negative impact on how we feel about our job. Luckily the SQL Server community is filled with really great people.
We have a job that checks all of our jobs looking for any jobs that has this set. You can use the query below to get the necessary information. There is a column, delete_level in sysjobs that will allow us to get the information needed.
, CASE delete_level
WHEN 0 THEN ‘Not Set’
WHEN 1 THEN ‘Delete on Success’
WHEN 2 THEN ‘Delete on Failure’
WHEN 3 THEN ‘Delete on Completion’
END AS ‘Delete Level Setting’
So why do I think this is potentially dangerous? Image the disruptive code that could be written and then imagine that same code in a job that is scheduled to execute 6 months later. Now go one step further, all evidence of that job running is now gone. I really don’t want to give too much information, but I can think of several things that could be done that could cost the company a ton of money. This is why we check for this.
I also include this check on my server assessment scripts.
As I said earlier, if anyone can give me a good reason to have this set, I am willing to listen and will update this post accordingly. Giving proper credit of course.
My suggestion is to look for this!
Thanks for visiting my blog!!!!