You are currently reviewing an older revision of this page.
Document Information
Document Type: TechNote
Product(s): SELECT Server
Version(s): 08.11.00.00 - Present
Original Author: Claudio Badalamenti, Bentley Technical Support Group
Overview
SQL Server service shuts down due to inactivity and the database "closes down" when no connections are active. There may also be a decrease in query performance, database maintenance operations, or regular transaction operations. In reviewing the SQL Server Log files you may notice the following errors:
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Note: This behavior does not occur in Microsoft SQL Server 2008.
Solution
The cache flush errors are related to the database being set to “Auto Close”, this should be disabled.
Option 1 -Using the MicroSoft SQL Server Management Studio, open the database properties click options and set the Auto Close to “False”.
Option 2 –From the command prompt, run the ALTER DATABASE command using “SQLCMD”.
ALTER DATABASE (DATABASENAME) SET AUTO_CLOSE OFF
Using the Windows “Trusted” Connection SQL Server 2005SQLCMD -S SERVER\SQL Instance -E
(Using a SQL administrator account)SQLCMD -S SERVER\SQL Instance -U account name -P password
See also: http://support.microsoft.com/kb/917828