AARGH! Syscacheobjects showing my cache keeps clearing out!

What could cause this? Normally it looks quite good but just this afternoon
I've watched it completely clear and everything start over like 3 times
now!! I can't seem to figure out what is causing it...I've never seen this
happen.
Any ideas anyone? Our busiest part of the week is coming soon, I'd hate to
have everything in recompile mode when it hits.
Thanks in advance...Memory pressure will cause that. How much memory does your server have, and
what is your max server memory set to?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uwWSqa5eGHA.4776@.TK2MSFTNGP05.phx.gbl...
> What could cause this? Normally it looks quite good but just this
> afternoon I've watched it completely clear and everything start over like
> 3 times now!! I can't seem to figure out what is causing it...I've never
> seen this happen.
> Any ideas anyone? Our busiest part of the week is coming soon, I'd hate
> to have everything in recompile mode when it hits.
> Thanks in advance...
>|||I believe an reconfigure command will do it as well. Have someone been
opening the server properties tab in Enterprise manager?
Check you SQL error log for configuration change alerts.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23ac6Jq5eGHA.5088@.TK2MSFTNGP02.phx.gbl...
> Memory pressure will cause that. How much memory does your server have,
> and what is your max server memory set to?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uwWSqa5eGHA.4776@.TK2MSFTNGP05.phx.gbl...
>|||Server has 6 gigs.
Max Server memory is set to 2147483647...
I've never seen more than ~ 3.5 gigs committed
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23ac6Jq5eGHA.5088@.TK2MSFTNGP02.phx.gbl...
> Memory pressure will cause that. How much memory does your server have,
> and what is your max server memory set to?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uwWSqa5eGHA.4776@.TK2MSFTNGP05.phx.gbl...
>|||Strange message...what would cause an option to change to the same value?
Error: 15457, Severity: 0, State: 1
Configuration option 'show advanced options' changed from 1 to 1. Run the
RECONFIGURE statement to install.
"Danny" <nomailbox@.nowhere.com> wrote in message
news:8Rvbg.1591$Ar6.1261@.trnddc02...
>I believe an reconfigure command will do it as well. Have someone been
>opening the server properties tab in Enterprise manager?
> Check you SQL error log for configuration change alerts.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23ac6Jq5eGHA.5088@.TK2MSFTNGP02.phx.gbl...
>|||Ok, more questions:
Is this 2000 or 2005? Standard or Enterprise Edition? Are you running
AWE/PAE? Do you have the /3GB switch enabled? Is other software running on
the server? x86 or 64-bit?
When I've seen this issue caused by memory pressure, it was due to max
server memory being set a bit higher than it should have been. It's
important to reserve some RAM for other processes running on the system, and
not let SQL Server take it all. You should throttle down that setting for
your server to something a bit more realistic (4.5 GB, perhaps, assuming
you're running Enterprise Edition on on x86 server with AWE and /3GB
nenabled, with no other software running on the server). For a bit more
background, see:
http://www.sql-server-performance.com/awe_memory.asp
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:edEOD2BfGHA.2172@.TK2MSFTNGP04.phx.gbl...
> Server has 6 gigs.
> Max Server memory is set to 2147483647...
> I've never seen more than ~ 3.5 gigs committed
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23ac6Jq5eGHA.5088@.TK2MSFTNGP02.phx.gbl...
>|||Hmmmmm I see where you're headed. It is Windows Enterprise Server 2003...
SQL 2000 3/GB switch IS NOT set. That's our problem right there. 32-bit
hardware, we are prepping our new 64-bit server with x64 windows and SQL2005
x64 as I write this. Production with that is at least a month out though.
Thanks...the memory limits totally eluded me.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uHqfA8BfGHA.3692@.TK2MSFTNGP03.phx.gbl...
> Ok, more questions:
> Is this 2000 or 2005? Standard or Enterprise Edition? Are you running
> AWE/PAE? Do you have the /3GB switch enabled? Is other software running
> on the server? x86 or 64-bit?
> When I've seen this issue caused by memory pressure, it was due to max
> server memory being set a bit higher than it should have been. It's
> important to reserve some RAM for other processes running on the system,
> and not let SQL Server take it all. You should throttle down that setting
> for your server to something a bit more realistic (4.5 GB, perhaps,
> assuming you're running Enterprise Edition on on x86 server with AWE and
> /3GB nenabled, with no other software running on the server). For a bit
> more background, see:
> http://www.sql-server-performance.com/awe_memory.asp
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:edEOD2BfGHA.2172@.TK2MSFTNGP04.phx.gbl...
>|||Don't forget to throttle it down on the 64-bit server, as well. I recommend
setting max to ~1.5, or even as much as 2 GB below the amount of memory the
server has, in 64-bit environments. This leaves room for other memory pools
that SQL Server utilizes (which are not necessarily included in that max
setting).
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:um6x9JCfGHA.4892@.TK2MSFTNGP02.phx.gbl...
> Hmmmmm I see where you're headed. It is Windows Enterprise Server 2003...
> SQL 2000 3/GB switch IS NOT set. That's our problem right there. 32-bit
> hardware, we are prepping our new 64-bit server with x64 windows and
> SQL2005 x64 as I write this. Production with that is at least a month out
> though.
> Thanks...the memory limits totally eluded me.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uHqfA8BfGHA.3692@.TK2MSFTNGP03.phx.gbl...
>|||Enterprise Manager does this in the configure GUI, for instance (I believe).
Anyhow, do a profiler
trace and see what app does this. You can also monitor the time when RECONFI
GURE is executed and the
cache is cleared.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uduxg4BfGHA.5088@.TK2MSFTNGP02.phx.gbl...
> Strange message...what would cause an option to change to the same value?
'
>
> Error: 15457, Severity: 0, State: 1
> Configuration option 'show advanced options' changed from 1 to 1. Run the
RECONFIGURE statement to
> install.
>
>
> "Danny" <nomailbox@.nowhere.com> wrote in message news:8Rvbg.1591$Ar6.1261@.
trnddc02...
>|||The sp_configure command includes a call to DBCC FREEPROCCACHE.
For the other issue, if someone sets 'allow updates' to 1, you will get
this message if the value was already 1.
So you need to run a trace to see who is running sp_configure, and/or you
can replace sp_configure with a procedure that has the DBCC FREEPROCCACHE
commented out.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uwWSqa5eGHA.4776@.TK2MSFTNGP05.phx.gbl...
> What could cause this? Normally it looks quite good but just this
> afternoon I've watched it completely clear and everything start over like
> 3 times now!! I can't seem to figure out what is causing it...I've never
> seen this happen.
> Any ideas anyone? Our busiest part of the week is coming soon, I'd hate
> to have everything in recompile mode when it hits.
> Thanks in advance...
>