AUTO_CLOSE

If SQL Server existed at the time of the Inquisition, then for the inclusion of some options on the production servers would have to be punished with a hot iron. But if we discard the lyrics, then we’ll look at why we don’t need to turn on AUTO_CLOSE and what the use of this option may cause

Actually, like any interesting life stories, it all began with a routine task.

The other day I had to look into the Error Log on a test server. In the second minute of waiting, SSMS pretty poplohlo from the abundance of messages that kept the magazine, and I decided to see how much the logs weigh using xp_enumerrorlogs :
')

DECLARE @t TABLE (lod_id INT PRIMARY KEY, last_log SMALLDATETIME, size INT) INSERT INTO @t EXEC sys.xp_enumerrorlogs SELECT lod_id, last_log, size_mb = size / 1048576. FROM @t 

 lod_id last_log size_mb -------- --------------------- --------------- 0 2016-01-05 08:46:00 567.05288505 1 2015-12-31 12:53:00 1370.39249420 2 2015-12-18 11:32:00 768.46394729 3 2015-12-02 13:54:00 220.20050621 4 2015-12-02 13:16:00 24.04152870 5 2015-11-16 13:37:00 80.07946205 6 2015-10-22 12:13:00 109.33527946 

As a rule, I try not to deal with the log size on test servers, because every time SQL Server starts, the log files change cyclically: the current errorlog is renamed to errorlog.1 , an empty errorlog file is created and the oldest errorlog.6 is deleted.

In the case when it is necessary to clear the logs, sp_cycle_errorlog can come to the rescue . But before clearing the journals, I still wanted to understand that they contained such interesting things.

Made a read from the current log using the xp_readerrorlog stored procedure:

 EXEC sys.xp_readerrorlog 

And then my peripheral vision caught the eye of numerous messages:

 Starting up database '...'. 

On the one hand, there is nothing wrong with that. Each time SQL Server starts, it opens the data files and checks the boot page:

 Starting up database '...'. CHECKDB for database '...' finished without errors on ... (local time). 

But after I filtered by the desired post, the sample results made me think:

 DECLARE @t TABLE (log_date SMALLDATETIME, spid VARCHAR(50), msg NVARCHAR(4000)) INSERT INTO @t EXEC sys.xp_readerrorlog 0, 1, N'Starting up database' SELECT msg, COUNT_BIG(1) FROM @t GROUP BY msg HAVING COUNT_BIG(1) > 1 ORDER BY 2 DESC 

 ------------------------------------------------------ -------------------- Starting up database 'AUTOTEST_DESCRIBER'. 127723 Starting up database 'MANUAL_DESCRIBER'. 12913 Starting up database 'AdventureWorks2012'. 12901 

A large number of such messages may occur due to the inclusion of the AUTO_CLOSE option .

According to the documentation, when the AUTO_CLOSE option is enabled, the database will automatically close and release all the resources it occupies when the last user connection using this database ceases to exist. When you re-apply the database will automatically re-open ... and so on to infinity.

Once upon a time I read that at the physical level, the AUTO_CLOSE operation for older versions of SQL Server was a completely synchronous process that could cause large delays with constant reopening of the database files. Starting with SQL Server 2005 , AUTO_CLOSE became asynchronous and some of the problems were gone. And what's left? Just enough to not use this option ...

To optimize performance, SQL Server performs page changes in the buffer cache and does not write these pages to disk after each modification. Instead, SQL Server periodically creates a checkpoint on which it writes the current pages that have been modified in memory, along with the transaction log information from memory to disk. When you close the database, CHECKPOINT is automatically executed. Accordingly, with constant DB closures, the disk load can greatly increase.

Also, each time the database is closed, its procedural cache is cleared. Accordingly, when the database reopens, you will have to generate execution plans for the new one. But what is even sadder ... when closing, the buffer cache is also cleared, which increases the disk load when executing queries.

Microsoft is solidary with me and also does not recommend including AUTO_CLOSE :

There is a possibility for your team to go ahead and you’ll be able to complete the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection.

However, there are a couple of nuances. If you are using SQL Server 2000 or any Express edition, then when creating a new database, the AUTO_CLOSE option will implicitly turn on:

 USE [master] GO IF DB_ID('test') IS NOT NULL DROP DATABASE [test] GO CREATE DATABASE [test] GO SELECT is_auto_close_on FROM sys.databases WHERE database_id = DB_ID('test') 

 is_auto_close_on ---------------- 1 

Bravo Microsoft ! Standing ovations ...

Although, if you look at it from the other side, this behavior is quite understandable for SQL Server Express , because within this edition there is a limit on the size of the RAM used - no more than 1 GB.

But for the future, if you suddenly need to deploy the database using a script, it is better to be safe and explicitly disable AUTO_CLOSE :

 ALTER DATABASE [test] SET AUTO_CLOSE OFF 

In the course of work, I noticed another interesting point - when accessing some functions or system views, all databases with the AUTO_CLOSE option enabled will open:

 USE [master] GO IF DB_ID('p1') IS NOT NULL DROP DATABASE [p1] GO CREATE DATABASE [p1] GO ALTER DATABASE [p1] SET AUTO_CLOSE ON GO IF DB_ID('p2') IS NOT NULL DROP DATABASE [p2] GO CREATE DATABASE [p2] GO ALTER DATABASE [p2] SET AUTO_CLOSE ON GO EXEC sys.xp_readerrorlog 0, 1, N'Starting up database ''p' GO 

 LogDate ProcessInfo Text ----------------------- ------------ ---------------------------------- 2016-01-25 17:36:40.310 spid53 Starting up database 'p1'. 2016-01-25 17:36:41.980 spid53 Starting up database 'p2'. 

We refer to p1 :

 WAITFOR DELAY '00:03' GO SELECT DB_ID('p1') GO EXEC sys.xp_readerrorlog 0, 1, N'Starting up database ''p' 

But p2 also wakes up "for the company":

 LogDate ProcessInfo Text ----------------------- ------------ ---------------------------------- 2016-01-25 17:36:40.310 spid53 Starting up database 'p1'. 2016-01-25 17:36:41.980 spid53 Starting up database 'p2'. 2016-01-25 17:39:17.440 spid52 Starting up database 'p1'. 2016-01-25 17:39:17.550 spid52 Starting up database 'p2'. 

And finally, we got to the bottom of the truth. On the server, various users actively accessed metadata ... this made the databases wake up with AUTO_CLOSE enabled, which in turn implicitly caused the log to grow.

Preventive measures, by the way, came out very simple:

 DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = ( SELECT ' ALTER DATABASE ' + QUOTENAME(name) + ' SET AUTO_CLOSE OFF WITH NO_WAIT;' FROM sys.databases WHERE is_auto_close_on = 1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') EXEC sys.sp_executesql @SQL 

Everything was tested on Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64).

If you want to share this article with an English-speaking audience:
Enabling AUTO_CLOSE is a bad idea?