SQL: Is AUTO_CLOSE always evil?

I can’t tell you how many SQL Server articles that I’ve read that suggests that AUTO_CLOSE is a completely evil option. I understand the sentiment, but is that really true? If so, why does it exist in the first place?

What is the AUTO_CLOSE option?

When SQL Server is started, it normally opens all database files that are meant to be ONLINE, and it keeps them open until you shut down.

When you enable AUTO_CLOSE for a database, SQL Server doesn’t open the files when you start it. When a user tries to access the database, the files are opened at that time instead. And they stay open while any user is using the database. When no users are connected to the database, the files for that database are closed again.

Why AUTO_CLOSE exists

Many will not remember but SQL Server was supported on the Windows 98 operating system, using the FAT32 filesystem. FAT32 wasn’t a journalled filesystem and was easy to corrupt. One common cause of corruption was to just turn the server off, rather than shutting it down cleanly.

The last thing that the SQL Server team wanted, was to have database corruption caused by filesystem corruption, particularly if the database wasn’t being actively used at the time the issue occurred.

So, the AUTO_CLOSE was added as an option, to make sure the files were closed if the database wasn’t being used. That greatly reduced the possibility of database corruption caused by the filesystem.

And SQL Server made it the default option on those systems.

How did it spread?

Some users who didn’t know what they were doing did enable this feature, but that wasn’t the main way it got deployed.

Databases with AUTO_CLOSE ended up all over the place, because developers who had Windows 98 on their laptops, created databases that were backed up and restored onto servers, as part of their initial deployment.

And AUTO_CLOSE is one of the options that stayed enabled when you restored a database. So servers ended up with databases with AUTO_CLOSE enabled because that’s how it started.

Example disaster from AUTO_CLOSE

Periodically, I’ve done work for Microsoft where they have had issues at client sites that need resolving. It’s generally only bizarre things.

When I lived in Queensland, I remember Microsoft asking me to visit a government client because they had SQL Server performance issues that no-one seemed to be able to fix.

And sure enough, when I watched them trying to use their application, it was glacial, performance-wise. Worse, they had tried throwing hardware at the issue for quite a while. They had deployed it on equipment that most people could only dream of using, and yet it was still painfully slow.

While I was looking into it, I noticed in the SQL Server logs that the database was constantly being opened and closed. And that was the issue. In their application, every single UI element was separately bound back to the database. As a user moved from UI element to another UI element, the only connection was being closed, and later reopened. The irony is that if they had tested it with a bunch of users connected, they would not have seen the issue. But with a single user constantly opening and closing connections, it was a disaster.

No surprise that disabling AUTO_CLOSE fixed the issue immediately.

This is a good reminder too, that checking SQL Server logs can be very, very useful, and I’m amazed at how many people never look into them.

So it is ever useful and always evil?

Generally today, having it disabled is a good idea, and should always be your default. I don’t agree though, with users who say it shouldn’t exist as an option. I have seen scenarios where it was helpful.

As an example, I did work for a mining company and they had a database for each drilling site that they used. Even though there were tens of thousands of drilling sites, only a handful would ever be in use at any point in time.

Just imagine how long it takes to start a server with 20,000 databases, when it needs to open all the files, particularly when it only needed a handful open.

And if you’ve ever tried to use SQL Server tooling like SQL Server Management Studio with tens of thousands of databases, you’ll understand that it’s a real problem too.

At that site, AUTO_CLOSE was the perfect option. All the files stayed closed. Startups were fast, and SSMS worked fine. But when a site started working, and opened a connection, that particular database had its files opened. When they stopped working on that site, the files were closed.

In that scenario, AUTO_CLOSE is totally helpful. Clearly that’s not a common sort of scenario, but calls to have the option removed totally, haven’t thought these sorts of issues through.

Mind you, for these types of scenarios today, I’d be considering a serverless database in Azure. But that’s not always an option.

2025-06-11