AUTOCLOSE and SQL Server 2005
Sometime ago I imported a small MSDE database into SQL Server 2005. Overtime the database has grown significantly and is now over 100gb. For the first time last week we had to do some maintenance which necessitated shutting down and restarting the SQL Service.
When SQL came back up I noticed that the database in question was in recovery mode and was not accessible. I left it for a about an hour and a half whilst checkdb completed, however, even after checkdb had run without errors the database was still in recovery mode.
Checking the logs I noticed the following pattern.
Message
Message
Starting up database 'dbname'.
CHECKDB for database 'dbname' finished without errors on 2008-10-23 11:25:42.580 (local time). This is an informational message only; no user action is required.
Message
Starting up database 'dbname'.
After some investigation I spotted that AUTOCLOSE was set on the database. Which presumably must have been imported when I migrated the database from MSDE.
According to MSDN when AUTOCLOSE is 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 doesn’t work so well on a 120gb database!
To turn it off use
ALTER DATABASE 'database_name'
SET AUTO_CLOSE OFF
Problem solved.
