Tuesday, July 24, 2012

SQL Server has encountered 1 occurrence(s) of cachestore flush


SQL Server Log report from one of our production server was continuously showing following messages.
Log Date
Process Info
Process Text
2012-07-23T20:00:08.880
spid17s
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.
2012-07-23T20:00:08.880
spid17s
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.
2012-07-23T20:00:07.190
spid17s
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.
2012-07-23T06:00:04.640
spid16s
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.
2012-07-23T06:00:04.640
spid16s
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.
2012-07-23T06:00:04.580
spid16s
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.

It happens when you configure user database with Auto Close option.
SQL Server, close a user database automatically, when last session is closed and reactivated when a login request is received.  We must keep this option OFF for a better performance. Why so read this.

No comments:

Post a Comment

All suggestions are welcome