SQL Interview: 61 Effects of AUTO_CLOSE

SQL Interview: 61 Effects of AUTO_CLOSE

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Medium

Question:

You are reviewing database settings for a server.

You notice that one database has AUTO_CLOSE configured. Your model database does not have it enabled.

What is the most likely cause for that setting to be enabled, and what effect does enabling it have?

Answer:

The most likely cause of AUTO_CLOSE being enabled for a database that’s on a server where the model database has it disabled, is that either:

  • It was intentionally set, likely for an inappropriate reason
  • The database was restored from elsewhere and already had the setting enabled.

The impact of enabling it is that the files for the database are not opened when SQL Server starts. When there are no users connected to the database and a user connects to the database, the files are opened at that time. When the last user disconnects from the database, the files are closed (rather than when the server is shut down).

2025-07-07