Tag: auto delete

Auto Delete Jobs????

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.

      1. When the job secceeds
      2. When the job fails
      3. 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.

SELECT    name
  , delete_level
  , 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’
FROM sysjobs

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!!!!

ADS – AutoClose Database Setting

Recently when working with Azure Data Studio, I noticed something I hadn’t seen before. I noticed to the right of the database name the words “Auto Closed”, as in the image below.

Let’s take a quick second to go over exactly what Auto Close is and what it means.  Auto Close is a database level setting that can be set to either On or Off.  When this is set to On, SQL Server behaves slightly different.  SQL Server will open and close the database for each connection.  While this creates additional, usually unneeded overhead, this isn’t the only concern.  When Auto Close is set to ON, it will free up all the resources it is currently consuming, including the plans in the plan cache.

To turn this on or off, you will need to go to the settings of the database.

Having this is to ON, can cause a performance issues and more entries in the log.  Below is an example of what entries you may see in your log.

In the below image you can see the database that has been flagged as having been closed automatically.

This is something new in Azure Data Studio.  SQL Server Management studio does not display this, as shown in the image below.

Once you query a table, the “Auto Closed” message will go away.  The reason it will go away is because the database is no longer closed.

Now that we have covered what Auto Close is and how it will be displayed in Azure Data Studio, you might be wondering whether or not it should be turned ON.  The answer is NO…you should not turn this on.  I think Pinal Dave summed it up the best in the screenshot of something from one of his blog posts, the screenshot is below.  This is the link to this page.

In addition to what Pinal says, there many others that also recommend that this setting be turned OFF.  This post really wasn’t about the inner workings of the Auto Close setting, but I felt it was important to give a brief overview of what it is.  Read some of the other posts and make up your own mind, but keep in mind it is considered a best practice to leave this off.

Thanks for visiting my blog!!