Plan Cache notification messages in SQL 2k5

  • Hi, Recently I have started getting the following message under the Application Event Viewer. I get these messages around twice every hour. Do I have to take any action for this event? Is there something wrong? What does the Plan cache stuff mean?

    Here is the message.

    Event Type:Information

    Event Source:MSSQLSERVER

    Event Category:(2)

    Event ID:2803

    Date:5/9/2007

    Time:2:05:33 PM

    User:N/A

    Computer:SERVER

    Description:

    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.

    Thanks.

  • i'm getting the same but not as frequent. i read this:

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

    and none of those commands that trigger this message are being ran unless its and internal sql thing??

    I'm getting them at:

    17:20 (nothing running at that time)

    20:00 (nothing running at that time)

    21:25 (backups have just finished)

    any ideas?

    _________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

  • Hi

    Possibly switch "Autoclose" to False in the DB options...

    Worked for me.

    mediademon


    Best Regards,

    Mediademon
    www.mediademon.com

  • Hi,

    I have also observed the same message in SQL Server Error log. I the rechecked the Database property for 'IsAutoClose' for all the databases in my server. But this property for all these databases has been set to be false.

    And till it generates the error.

    Is any other issue responsible for this behavior ?

    Thanks in Advance.

    Arindam.

  • Are they happening at regular intervals? It may be worthwhile running profiler for a while to see where the commands are coming from and from who.

    The things that cause that message:

    Database restore

    Someone running DBCC FREEPROCCACHE

    Autoclose

    Changing some setting with sp_configure

    There are probably others

    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
  • transaction log restores will also cause this proccache clear down, so if you are log shipping this will happen at regular intervals.

    If this irritates you as it doe me , please vote:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=342832

    ---------------------------------------------------------------------

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

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