Friday, November 18, 2016

Is Auto Close Enabled in your SQL Server database?

In SQL Server, one of the Database Properties options is Auto Close. This is an option to be used (auto close = True) if our intention is to shut down cleanly and free the resources once the last user accessing that database exits. If after the last user exits we still want to keep the database alive without shutting down then set auto close as FALSE.

But having this setting enabled in a Production environment will end up with performance issues majority of the times. Why? Because once the DB is closed all cached items (data / procedure cache, execution plans) will be flushed out as well. So it has to work from the scratch again when the next user connects in.

How to know what is it set to?

Option 1: Try this query which will return all databases in that server where Auto Close is set to TRUE.

SELECT [name] AS [DatabaseName] FROM SYS.databases 
WHERE is_auto_close_on = 1

--To change AUTO CLOSE option to FALSE
USE [master]
GO
ALTER DATABASE [TEST] SET AUTO_CLOSE OFF;

Option 2: In SQL Server Management Studio, right-click on the database of interest, click on “Properties”, and choose the “Options” tab to see a dialogue box similar to the one shown below


Change that value to False and click OK.

Best practice is to keep this set to FALSE. Still not convinced? my suggestion would be if you’re not too sure whether this setting is helping you or not then have it set to FALSE and/or seek help from experienced DBAs.

No comments: