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

Question

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

Admingod

SSCertifiable

Points: 5997
More actions
November 30, 2011 at 2:45 pm

#263913

I am getting below error when i try to restore database in sql server 2005
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.
Any workaround?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

That's not an error. It's just telling you that the plan cache has been cleared. A restore of a database always clears the plan cache.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Admingod SSCertifiable Points: 5997 More actions · Answer 2

I initiated the restore but i still see this message in sql server logs again and again?Restore is taking forever not sure why?

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.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Restoring a database always causes a flush of the plan cache. Hence, any time you restore a DB you'll see those messages.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Admingod SSCertifiable Points: 5997 More actions · Answer 4

Since one hour restore completed only 2% which is unusual for me?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Got nothing to do with those messages.

Restore time is mostly about IO.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Admingod SSCertifiable Points: 5997 More actions · Answer 6

I also noticed this message in error logs? Because of this restore is taking longer than normal?

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file

The OS file handle is 0x00000000000006DC. The offset of the latest long I/O is: 0x000022b6dde000

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file in

The OS file handle is 0x000000000000082C. The offset of the latest long I/O is: 0x00003d8303b000

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file

The OS file handle is 0x0000000000000778. The offset of the latest long I/O is: 0x00000000108000

SQL Server has encountered 1918 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file

Is anything to do with this?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Probably. Those indicate that the IO subsystem is under strain. Could be that it's just not got the throughput needed or could be there's something else running as well (file copy is a possibility) that's affecting the IO subsystem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sanket kokane Hall of Fame Points: 3465 More actions · Answer 8

Check if the auto-close option for database is on,if yes make it false

-----------------------------------------------------------------------------
संकेत कोकणे

sinanselamoglu Valued Member Points: 65 More actions · Answer 9

Hello,

For I/O Problem, you can check your SAN disk. But, I strongly advise to check SQL Server Memory Configuration.

(Minimum Server memory & Maximum Server Memory) If there is some memory bottleneck, it directly gives this error.

BR;

maxdodti Grasshopper Points: 19 More actions · Answer 10

This always happens when user are executing queries and log restoration starts.

The restoration takes time, check weither any deletion or any developer patches applied on the tables.

If the records are updated are more the new rowid in the header has to matches with the old header.so the restoration will take time.