Click on the banner to learn about and purchase my database training on Azure

SQL Server - Is your instance constantly with messages "Starting up database XXX" and databases "In Recovery"? Get to know Auto Close

Views: 1.100
Reading Time: 5 minutes

Hello everybody!
All beauty?

In this post I would like to write about a feature of SQL Server that even today, we see being implemented in instances of various companies and which causes all resources (CPU, Disk, Memory ..) to be released after the last user. get rid of the databases. I am talking about the Auto-Close property, which is considered by many to be one of the WORST practices in administering SQL Server instances.

Explaining the Auto Close Feature

According to Microsoft documentation, when the Auto Close feature is enabled on a database, all features related to that database are released and the database is closed after the last active user disconnects from that database. If a user tries to access this bank again, the server resources are reallocated, the bank is reopened, and available.

In theory, this feature appears to be advantageous, as it saves server resources and only uses them when necessary. However, in practice what happens is the reverse: There is a cost to bring down inactive connections, file manipulation and buffer allocations to “shut down” the database when it is closed automatically. This cost is even greater when having to "turn on" the database again when a new access is requested to that database when it is "off".

Since no caching is done, if the bank is closed by Auto-close and after 1 ms is requested again, the bank will have to deallocate all resources and relocate all within a range of 1ms. Imagine an instance where This often happens. The overhead of this operation, repeated over and over, requires a much greater effort than leaving resources allocated indefinitely, not justifying the use of this property.

In addition, this feature meets the benefits of SQL Server Connection Pooling, which are widely used by .NET applications, which aims to reduce the high cost of having to open and close connections with each command sent to the database. data.

Another downside to using this property is that you will start to see several and several events in the SQL Server log with the message “Starting up database your_database”, as shown below:

This is very bad for instance administration because it generates a very large amount of information in the instance log and distracts from alerts that may actually indicate an alert or problem in the instance.

To further worsen the life of the DBA that usually activates this property, some SQL Server DMV's, which you use for performance tuning, validations and information extraction, end up having their values ​​reset each time the database goes offline, making you does not have reliable information to perform this type of analysis.

Another negative point of Auto Close, is that I have already seen some cases in which it ends up generating a “confusion” in the undocumented procedure master.dbo.sp_MSforeachdb, causing it, in its loop of iterations between databases, to end up not listing a database that has Auto Close enabled, and therefore does not execute the command that should be executed. This is because this procedure only executes commands in databases whose situation is "ONLINE". With Auto Close enabled, it is common for it to be in the “In Recovery” state when exiting the inactive state when a user logs in.

If you are still not satisfied with all the arguments presented, this property is marked deprecated (Reference Link), that is, it will be removed in some future version of SQL Server and should no longer be used in new environments and newly created instances.

In fact, you shouldn't even be using Auto Close.

In which scenarios should Auto Close be used?

Because it is considered a bad practice among DBA's SQL Server, it is very unlikely that you will find a scenario where Auto Close should be used.

One scenario that I think would justify is when you have a database on a server with low CPU and memory resources, which is accessed once a day, by a SQL Agent job or a specific query. This query would execute a processing routine reading the data from this database and after this processing, no job, user or application would read, write or access to this database.

As you may know, in the real world this scenario is very unlikely to happen and it is hard to have the assurance that no one else will use the database, so even if this scenario exists, in the future you can risk this scenario changing and your instance suffer this overhead generated by Auto Close.

How to identify if a database has Auto Close enabled?

To identify the instances that have the Auto Close property enabled, just right-click on a database, select the “Properties” option

On this screen, select the “Options” category and look for the “Auto Close” property on the Grid.

How to identify databases with Auto Close enabled on instance using T-SQL?

To identify databases with Auto Close enabled on the instance using T-SQL, you can execute the query below:

Result:

How to disable Auto Close using T-SQL?

To disable the Auto Close property of a database in your instance, use the command below:

If you want to apply this command to all databases in your instance, you can use this command:

That's it!
I hope you enjoyed this tip.

Regards and see you next post.

sql server database how to identify disable how to disable auto close property property option option all databases

sql server database how to identify disable how to disable auto close property property option option all databases