SQL Server - Is your instance constantly having “Starting up database XXX” and “In Recovery” databases? Meet Auto Close

Views: 815
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 seems to be advantageous as it saves server resources and only uses them when needed. However, in practice, the opposite is true: There is a cost to dropping idle connections, file manipulation, and buffer allocations to “shut down” the database when it is closed automatically. This cost is even higher when you have to “turn on” the database when new access is requested to that database when it is “turned 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 seeing multiple and multiple 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 make life even worse for the DBA that usually enables this property, some SQL Server DMV's, which you use for performance tuning, validating, and extracting information, will set their values ​​to zero each time the database goes offline, causing you to do not have reliable information to perform this type of analysis.

Another downside of Auto Close, is that I've seen some cases where it ends up causing a “mess” 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 thus not executing the command that should be executed. This is because this procedure only executes the commands in databases whose situation is “ONLINE”. With Auto Close enabled, it is often in Auto Recovery state when exiting the idle state when a user makes an access.

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's considered a bad practice among SQL Server DBA's, it's very unlikely that you'll 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 instances that have the Auto Close property enabled, simply right-click on a database, select the “Properties” option.

In this screen, select the "Options" category and look for the "Auto Close" property in 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