Dave Bland takes us through SQL Agent job auto-deletion:
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.
I’ve used it in the past for scheduling ephemeral work, particularly when I didn’t have the ability to control operations otherwise. For example, I need to perform a time-consuming one-time update on data, but I don’t want to tie it to a script on my machine because I wanted to go home that night. Creating a job which auto-deletes upon success lets me schedule it for when I want it to run, kick off the script, and not leave a mess behind in the SQL Agent jobs list. It’s a case where I don’t really care about history and checking the box gives me a quick indicator of success: if the job’s gone in the morning, my work here is done; if not, I need to begin troubleshooting.
How about using a one-time job to deploy a change to a production stored procedure that has to wait until after hours?
Yeah, that’s another good use for them.
We have do use it. We have a Master SQL job that will create threads (sub jobs) on the fly to perform maintenance. Since the sub jobs contain TSQL specific to the task at hand. Once done, the job is not necessary. The “Master” job has all thing info that is needed.