July 1, 2002 at 10:00 pm
Hey all
I am getting the following errors in the error logs on our test box...
Insufficient memory available..
LazyWriter: warning, no free buffers found.
Buffer Distribution: Stolen=7561 Free=0 Procedures=57
Inram=21953 Dirty=19 Kept=0
I/O=0, Latched=106, Other=0
Buffer Counts: Commited=29696 Target=29696 Hashed=22078
InternalReservation=222 ExternalReservation=3240 Min Free=464
Procedure Cache: TotalProcs=1 TotalPages=57 InUsePages=57
Dynamic Memory Manager: Stolen=5890 OS Reserved=312
OS Committed=134
OS In Use=275
Query Plan=162 Optimizer=0
General=5917
Utilities=34 Connection=52
Global Memory Objects: Resource=449 Locks=5277
SQLCache=16 Replication=2
LockBytes=2 ServerGlobal=43
Xact=12
Query Memory Manager: Grants=1 Waiting=0 Maximum=19873 Available=14905
which I forceably stopped via:
SQL Server is terminating due to 'stop' request from Service Control Manager.
This happens to large, rouge SQL queries running against the box. I cant login to the server at all, all connects timeout and the sql statement runs for a miniumm of 30mins before completing and returning control, unfortunatly, the instance seems to be in a state where any smaller queries return the same errors above.
The box has 512Mb ram (bugger all I know), and ive allocated 400 to the instance.
Anyone experienced this before and worked out a cleaner way or addressing the issue?
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 2, 2002 at 6:58 am
Hi all
OK, managed to repeat it on our support server, but its very spiratic. We have a large query, the programmer was running it via query analyser and was chewing around 70% cpu and memory usage raised to the fixed max quickly. We cancelled the query and waited for around 5mins, the job was still trying to cancel and the above mentioned errors occured, in this particular case stopping the service also failed and required a server reboot.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 2, 2002 at 8:04 am
Nothing more memory wont fix!
Andy
July 2, 2002 at 9:32 am
Tend to agree with Andy. Large queries are problematic in that you cannot limit the CPU devoted to the process and this can "lock" the box. It will come back when the query completes or the timeout is reached.
You might want to limit the time a query can execute (Server properties, connections tab). This will at least limit the effects slightly until you can get more memory. 400MB is not very much. Should go to 1GB at least.
Steve Jones
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply