Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2011-01-17 : 14:45:38
|
| Here's a wierd one. In spite of all the queries we are running. Our sys.dm_exec_query_stats is empty. Our sys.syscacheobjects has only 4 records, all from DBID 32767. And every query/stored proc call seems to be recompiling. Anyone have any ideas? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-17 : 15:39:26
|
| Databases in autoclose?Restores running (log shipping)Any messages about cache flush in error log?--Gail ShawSQL Server MVP |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-01-17 : 15:47:36
|
quote:
Originally posted by GilaMonster
Databases in autoclose?Restores running (log shipping)Any messages about cache flush in error log?
Thankyou. I will look into the log shipping. We got some new software running - DSClient - it has something to do with backups. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-17 : 16:47:21
|
| Not backups. Restores. Restore flushes the proccache, backup doesn't.What's max memory set to and how much memory is available?--Gail ShawSQL Server MVP |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-01-17 : 17:04:11
|
quote:
Originally posted by GilaMonster
Not backups. Restores. Restore flushes the proccache, backup doesn't.What's max memory set to and how much memory is available?--Gail ShawSQL Server MVP
Memory is good (max = 7000). Total Pages is 860,000. This funny stuff just started happening recently. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-01-17 : 21:13:33
|
| I stopped and restarted SQL Server and that worked! Not sure how this happened in the first place. See what happens tomorrow.I don't think it was a restore issue. It seemed like queries were getting wiped out of the cache always and in under a second, all Databases. I also checked autoclose - it was false. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-18 : 00:53:55
|
| Any messages about cache flush in error log?--Gail ShawSQL Server MVP |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-01-18 : 11:44:58
|
quote:
Originally posted by GilaMonster
Any messages about cache flush in error log?--Gail ShawSQL Server MVP
Thankyou. I looked in the error log and didn't notice anything that could of caused this weird problem. There were a few instances of 'Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore'. But I sometimes call the clear proc cache on purpose when testing. The problem I had yesterday wasn't a case of the cache being cleared out occasionally, it was wiping out after an instant of executing anything and everything. |
|
|
|
|
|
|
Proc Cache disabled?