
	Applies To

	Product(s):	SELECTServer
	Version(s):	08.11.00.00 - Present
	Environment:	N/A
	Area:	N/A
	Subarea:	N/A
	Original Author:	Claudio Badalamenti, Bentley Technical Support Group

Warning: Making these changes can cause serious, system-wide problems within your environment. Do not make these changes unless you are confident with these steps and understand the impact to your system and other applications running on it.

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 2005
SQLCMD -S SERVER\SQL Instance -E

(Using a SQL administrator account)
SQLCMD -S SERVER\SQL Instance -U account name -P password

SQL Server 2005 Cachestore Flush Error

Overview

Solution