Plan Cache notification messages in SQL 2k5

Question

Post reply

Plan Cache notification messages in SQL 2k5

Kushal Koolwal

Ten Centuries

Points: 1080
More actions
May 10, 2007 at 1:52 pm

#117140

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.

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

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

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 1

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

Mediademon SSC Enthusiast Points: 105 More actions · Answer 2

Mediademon

SSC Enthusiast

Points: 105

August 15, 2007 at 3:07 am

#726982

Hi

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

Worked for me.

mediademon

Best Regards,

Mediademon

Arindam Ganguly Old Hand Points: 303 More actions · Answer 3

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.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

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

george sibbald SSC Guru Points: 104210 More actions · Answer 5

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

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