Tuesday, June 1, 2010

spid 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.

This issue is because of Autoclose Option

Today i got a screenshot from one of our production servers that database is starting up frequently in the error log file. The following error find in the error log :

spid97 Starting up database 'MyDb'.
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.

I tested the following error and came to know what is happening exactly
WorkAround:
1. Generally this situation will come when we restore database from another server. When a database has been restored from another server it clears the existing plan cache.
2. To avoid this situation we need to set Auto_close option to Off / False.

If auto_close option is true it will freeup the resources when all the users disconnected from the database, but in the production environment we can't give guarantee that next second other user will not connect to database, becoz of this reason database will give startup message in the error log file, and it is a performance overhead.

Auto_close option set to be false to enable database mirroring also.

Auto_shrink option also set to be false.

No comments: