U¿ytkownik "eric rees" <eric@nospam.nationsnetwork.net> napisa³ w wiadomo¶ci
news:bfm4dt$93v$1@news.nationsnetwork.net...
> We are having issues with one of our SQL servers. The mem usage for
> sqlsrver.exe spikes, basically using all available memory. When that
> happens, IIS, which happens to be using some ASP pagoes, stops serving out
> pages at all. I have tried to stop and start the SQL process, but to no
> avail. The server it is running on is a 2.4Ghz P4 Xenon, 1GB of RAM.
>
Read something about sp_configure, especially min server memory, max server
memory and set working set size options.
I think You should set part of memory only for MsSQL use.
Maeby something like this (of course set Your own values)

use master
go
exec sp_configure 'show advanced option', 1
reconfigure with override
exec sp_configure 'allow updates', 1
reconfigure with override
exec sp_configure 'min server memory', 512
reconfigure with override
exec sp_configure 'max server memory', 512
reconfigure with override
exec sp_configure 'set working set size', 1
reconfigure with override
exec sp_configure

But be cousious, You must have this 512 MB for your SQL server or he will
not be able to start.
regards
Marcin D

clockworkoi (clockworkoi@poczta.onet.pl) writes:
> use master
> go
> exec sp_configure 'show advanced option', 1
> reconfigure with override
> exec sp_configure 'allow updates', 1
> reconfigure with override

There is no reason to turn on "allow updates" to change memory settings.
And if you ever turn it on, turn it off again once you are done. "Allow
updates" is about updates to system tables, and that setting should
definitely only be on in the very moment you perform an update to a
system table - and that should happen very rarely.




--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

U¿ytkownik "Erland Sommarskog" <sommar@algonet.se> napisa³ w wiadomo¶ci
news:Xns93C2E943AA68FYazorman@127.0.0.1...
> clockworkoi (clockworkoi@poczta.onet.pl) writes:
> > use master
> > go
> > exec sp_configure 'show advanced option', 1
> > reconfigure with override
> > exec sp_configure 'allow updates', 1
> > reconfigure with override
>
> There is no reason to turn on "allow updates" to change memory settings.
> And if you ever turn it on, turn it off again once you are done. "Allow
> updates" is about updates to system tables, and that setting should
> definitely only be on in the very moment you perform an update to a
> system table - and that should happen very rarely.
>
Allways something new Thanks for info.
regards
Marcin D