February 5, 2008 at 12:49 pm
Guys,
I started getting the following errors recently. They happen every day, at the same time, and last 5-10 minutes. During the time of this problem, we have a Purging Job Running, and no other job is running. And the purging job starts about 25 minutes prior to that and ends about 20 minutes after that. The errors are:
Insufficient memory available
Error: 17803, Severity: 20, State: 4
Query Memory Manager: Grants = 2 Waiting = 0 Maximum = 136099 Available 112441
Dynamic Memory Manager: Stolen=11173 OS Rserved = 13832
Procedure Cache: TotalProcs = 1697 TotalPages = 2719
...
In addition to the purging job, there are some stored procedures or possibly something else running against the database, and causing this problem.
As far as I understand, this has something to do with memory.
I decided to create a Profiler(Server) Trace to monitor what is going on during those times. Could someone suggest which are the best counters to capture in order to find out what the problem is?
Thanks a lot!
February 5, 2008 at 4:49 pm
It could be an issue with contiguous memory. Run xp_memory_size from Master database. This is an undocumented proc, but you can find information about it on the web. We see contiguous memory issues with LiteSpeed sometimes.
-SQLBill
February 5, 2008 at 4:54 pm
What does the purging job do? Is is one transaction or multiples? Many tables? Some larger? There might be an issue related to scale here. I'd actually profile where in the purging job you are when this happens and try to capture some metrics on scale (rows) when it occurs and when it doesn't to try and track things down.
I'd probably also watch memory, disk, transaction counters as well as CPU in Perfmon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply