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