Procedure Cache Dropping We've experienced an issue with SQL Server dropping proc cache out of the blue.

I'm talking 4GB down to 0 in 2 minutes. This has occurred approx once per day in the last two weeks.

It does happens at random times and does not coincide with process.

Has anyone seen this before?

So far as I'm aware the cache its only wiped if DBCC FREEPROCCACHE is executed or SQL is restarted.


  Version: Microsoft SQL Server 2005 - 9.00.4226.00 (X64)   May 26 2009
  14:58:11      Copyright (c) 1988-2005 Microsoft Corporation   Enterprise
  Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)


32GB RAM


Any help much appreciated.

We use Idera SQL Diagnostic manager.

Results:

DateTime                Proc Cache Size MB 
23/12/2015 19:19:00     4,165.63 
23/12/2015 19:25:00     4,165.71 
23/12/2015 19:32:00     4,178.93 
23/12/2015 19:38:00     4,175.44 
23/12/2015 19:44:00     4,176.87 
23/12/2015 19:50:00     4,179.80 
23/12/2015 19:57:00     207.16 
23/12/2015 20:03:00     459.66 
23/12/2015 20:09:00     510.48 
23/12/2015 20:15:00     1,795.31 
23/12/2015 20:21:00     2,830.94 
23/12/2015 20:27:00     2,859.87 
23/12/2015 20:34:00     2,877.93 
23/12/2015 20:40:00     2,891.94 
23/12/2015 20:46:00     2,908.82 
23/12/2015 20:52:00     2,921.34 
23/12/2015 20:58:00     2,975.92
One reason could be somebody changing options or running sp_configure. That would be logged in your error log.

Please read this article: Using Sp_configure To Change a Value Will Issue DBCC FREEPROCCACHE


  using sp_configure to change a configuration value causes SQL Server
  to issue a DBCC FREEPROCCACHE statement.


and


  The same behavior will occur if you use a GUI such as SQL Server
  Enterprise Manager that issues an sp_configure call that changes a
  parameter.


The Plan Caching and Recompilation in SQL Server 2012 white paper gives some other possibilities.


  Flush Entire Plan Cache The following operations flush the entire plan cache, and therefore, cause fresh compilations of batches that
  are submitted the first time afterwards:
  
  
  Detaching a database
  Upgrading a database to a later database compatibility level
  Restoring a database
  DBCC FREEPROCCACHE command
  RECONFIGURE command (many of the options to this command cause a flush)
  ALTER DATABASE … MODIFY FILEGROUP command
  Modifying a collation using ALTER DATABASE … COLLATE command
  
  
  Flush Database-Related Entries in Plan Cache The following operations flush the plan cache entries that refer to a particular
  database, and cause fresh compilations afterwards.
  
  
  DBCC FLUSHPROCINDB command
  ALTER DATABASE … MODIFY NAME = command
  ALTER DATABASE … SET ONLINE command
  ALTER DATABASE … SET OFFLINE command
  ALTER DATABASE … SET EMERGENCY command
  DROP DATABASE command
  When a database auto-closes
  


Lots of these events should be in your log files.