Quick Scan Report – Database Auto Close

What this check looks for

Online databases where DATABASEPROPERTYEX(name, 'IsAutoClose') is 1.

Why it matters

Every time the last connection closes, SQL Server shuts the database down. Every time somebody connects again, it opens it back up. Each cycle costs more than it saves.

What happens on close:

  • The database’s resources are released, which is the intended benefit.
  • Its plan cache is flushed. Every cached execution plan for that database is gone.
  • Its file handles are released.

And on the next connection:

  • The files are opened and the database is recovered.
  • Every query recompiles, because there are no cached plans.
  • The buffer pool has to read the pages back from disk.

So the first user after a quiet period gets a slow connection, slow compilation and cold reads, all at once. On a database with intermittent traffic, which is precisely the kind of database somebody enables this for, that is most users.

It also produces a great deal of error log noise. Each open and close writes entries, and on a database cycling several times an hour the log fills with them, burying everything else. That interacts badly with the error log checks elsewhere on this report.

And it makes monitoring unreliable. A closed database does not report size, does not appear in some DMVs the same way, and a monitoring tool connecting to check on it is itself what opens it, so the act of watching changes what is watched.

The memory it saves is negligible on any machine built this century. The setting comes from an era of SQL Server Desktop Engine and MSDE on workstations with very little RAM, and it is still the default for databases created by some application installers and for SQL Server Express in some configurations. Almost nobody turns it on deliberately.

How to confirm it yourself

SELECT [name],
       [is_auto_close_on],
       [is_auto_shrink_on],
       [state_desc],
       [user_access_desc]
  FROM sys.databases WITH (NOLOCK)
 WHERE [is_auto_close_on] = 1
 ORDER BY [name];

Check model too, or new databases inherit it:

SELECT [name], [is_auto_close_on] FROM sys.databases WITH (NOLOCK) WHERE [name] = 'model';

The noise it is making in the error log:

EXEC sp_readerrorlog 0, 1, N'Starting up database';

A database appearing there many times a day is this setting at work, and it is the most visible symptom.

How to fix it

One statement per database. Online, immediate, no locks:

ALTER DATABASE [YourDatabase] SET AUTO_CLOSE OFF;

Across everything that has it:

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'ALTER DATABASE ' + QUOTENAME([name]) + N' SET AUTO_CLOSE OFF;' + CHAR(13)
  FROM sys.databases WITH (NOLOCK)
 WHERE [is_auto_close_on] = 1 AND [state_desc] = 'ONLINE';
PRINT @sql;
EXEC sp_executesql @sql;

Check AUTO_SHRINK at the same time. The two are frequently on together, because they come from the same era and the same installers, and autoshrink is considerably more harmful. It has its own check.

Fix model, so this stops arriving with new databases:

ALTER DATABASE [model] SET AUTO_CLOSE OFF;

If the motivation was genuinely to free resources on a machine with many idle databases, the real answers are max server memory set appropriately so SQL Server does not over-consume, and consolidating databases that are idle enough to be worth closing onto fewer instances. Neither costs a recompile storm on every connection.

How long it takes

About an hour across an instance, most of it confirming which databases have it rather than changing them.

Report Why you would go there
Database Overview Database options including this one.
Plan Cache The cache being discarded on every close.
Error Log The startup entries this generates.
Databases By Size Which databases are affected and how large.
Memory Whether memory pressure was the original motivation.
Check
Database set to Autoshrink The setting this is nearly always found beside, and worse.
Auto create statistics is not enabled Another database option that is nearly always wrong.
Huge Error Log What the open and close noise contributes to.
Max server memory The setting that addresses memory pressure properly.

Frequently asked questions

The database is only used once a week. Is it not sensible then? Even then the saving is a few megabytes of metadata, and the cost is a slow first connection and a recompile of everything. If the database is genuinely idle, its memory is already being reclaimed by normal buffer pool pressure.

Does it need downtime to change? No. It takes effect immediately and takes no locks.

Why do our new databases keep getting it? model, or an application installer setting it explicitly at creation. Fix model first, then watch whether it comes back.

Is this why the first query of the morning is slow? Very possibly. A cold plan cache and a cold buffer pool on a database that closed overnight looks exactly like that.