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:
|
1 2 3 |
SELECT database_id, [name], user_access_desc, state_desc FROM sys.databases WHERE is_auto_close_on = 1 |
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:
|
1 2 3 4 5 |
USE [master] GO ALTER DATABASE [dirceuresende] SET AUTO_CLOSE OFF GO |
If you want to apply this command to all databases in your instance, you can use this command:
|
1 2 3 4 5 6 7 8 9 10 |
USE [master] GO DECLARE @Comando VARCHAR(MAX) = '' SELECT @Comando += 'ALTER DATABASE [' + [name] + '] SET AUTO_CLOSE OFF; ' FROM sys.databases WHERE is_auto_close_on = 1 EXEC(@Comando) |
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
I will use perfect very clear explanations Congratulations.
Thank you, Jorge.
Glad to hear you liked the article.
Hug!
Very interesting the post Dirceu! I liked the points observed and the simplicity of the explanation. It is a simple option but it generates a lot of impacts on the environment and many people are unaware of its usefulness.
Hug.
Luiz Vitor
Oops! Excellent post Dirceu! I had a problem this week in a production environment, after migrating a set of databases from a client into the datacenter where I work; I had not checked this property and realized the problem when executing the procedure master.dbo.sp_MSforeachdb. It did not return 1 of the more than 20 databases that were migrated in the report, and when opening the SQL log there were several database records restarting… most of them had auto close enabled, it was then when I did the analysis and changed all auto close to Off. I then included this check for the migration checklist 🙂
This precious and totally practical tip… Thanks for sharing!
Good tip Dirceu, many people have this parameter enabled and do not even know why. In fact, when using SQL Server Express it is very common to enable this parameter to save resources, it may then happen, from the developer or something, to send a backup to the client and the non-Express client, to stay there with this unnecessary parameter. activated.