Want your SQL Server to simply ignore the AUTO_CLOSE setting, for all open databases for which it has been enabled?

Under normal circumstances, once there is no one referencing any object from a database whose AUTO_CLOSE option is enabled, the database manager would attempt to close it (with the exception of master, tempdb and model system databases).

But if you want a given instance of SQL Server to simply skip closing any database whose AUTO_CLOSE setting is enabled you can do so by enabling trace flag 1808 globally. That is, either on startup, through the –T command line parameter or using the DBCC TRACEON command and ensuring you pass -1 as one of the parameters to it, like in DBCC TRACEON (-1, 1808).

And this trace flag is present on SQL Server 2005 (Yukon), 2008 (Katmai), 2008 R2 (Kilimanjaro), and 2012 (Denali).

Comments (0)