Microsoft SQL and ”Auto Close” option

MS SQL database properties have an option called Auto Close. Microsoft describes this option:

When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

This can be nice in some occasions, but it’s also quite annoying when you look at the logs on the server. There can be hundreds or thousands of lines related to opening a database, just because this setting is ON by default. In Windows Server 2012 when using ADFS services with WID (Windows Internal Database) this option should be switched to FALSE.

The change in pretty simple in all. Just open your Microsoft SQL Management Studio as Administrator and connect to:

\\.\pipe\Microsoft##WID\tsql\query

Then open your database properties and modify the Auto Close option to False.

SQL Server best practice states that it should be switched off.