cachestore flush for the ''Bound Trees'' SQL2005 SP2

  • Hi I have searched the MS Site for any information on this errors and have come up with No definitive answers as to why we are receiving this error?

    I searched the forum and have found another user suffering with the same or similar issues.

    What does it mean?

    Has anyone got a resolve?

    Many Regards

    Chris

  • Here you go:

    http://blogs.msdn.com/sqlprogrammability/archive/2007/01/17/10-0-plan-cache-flush.aspx

    Any restores, lately?

     

    Cheers,

     


    * Noel

  • I have been struggling with another issue which i have not been sure if it is related or not.

    having just recently migrated from 2000 to 2005 i have hit an issue with restoring a DB and having the restore spid Sit waiting with a wait type of 'MSSEarch'

    Scenario is . Backup of 2005 DB that has 3 FTC's. Performing a filelevel backup so as to not take the FTC files. Perform a restore into the same Instance using 'With Move' so as to have it as a different DB for testing purposes prior to migrating final changes to prod.

    Restore process puts the following messages into the event viewer

    • SQL Server has encountered x occurence(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 x occurence(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 x occurence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
    • Starting Up database '[dbname]'
    • The Database '[dbname]' is marked restoring and is in a state that does not allow recovery to be run
    • SQL Server has encountered x occurence(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 x occurence(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 x occurence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
    • Starting up database '[dbname]'
    • Recovery is writing a checkpoint in database '[dbname]'. this is an informational message only. no user action is required.

    At this point in time the DB in SSMS shows it as being available but when you try and access it an error msg comes up 'The database is not accessible'

    The restore processs is still running with the Wait Type of MSSEARCH.

    I am not able to Restart the MSSearch Service.

    If i try and kill the spid it goes into a killed\rollback state and just sits and does not complete.

    The only thing i have been able to do in the past is bounce the server for it to release the spids. as eventually this leads to blocking when the maintenance jobs come along to process and thus causing more blocking and increasing connections and timeouts with the application.

    The instance is running at SP2.

    I have had a look at the link posted above and yes this would indicate that the cacheflush messages i am receiving are from the restore. But this is a process that i have to get back working.

    Any suggestions and help on this would be appreciated.

    MCT
    MCITP Database Admin 2008
    MCITP Database Admin 2008
    MCITP Database Dev 2008
    www.jnrit.com.au/Blog.aspx

  • Noel,

    Thanks for that article. I turned "Autoclose" to off and the flushing stopped. So did the autofill of my eventlog.

    Sincerely,

    Steve S.

  • Smart move Steve

    Been looking for that solution for some time!

     


    Best Regards,

    Mediademon
    www.mediademon.com

Viewing 5 posts - 1 through 4 (of 4 total)

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