March 11, 2011 at 10:56 am
Hi,
I have a daily sql server agent job running that crashes each 3 days at one certain job step with error 802 when rebuilding an index. I do understand that there is a memory problem.
I can see from the server memory report that the buffer pool pages usage shows that all pages have been stolen after the job has crashed. What seems strange to me is that all pages remain stolen even when the job has ended.
Restarting the job from this step is not successful.
Only when executing
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
the job runs fine for a couple of days.
Can anybody explain this or has any hints on how to find the reason? I tried some perfmon counters (memory, buffer manager, processor) and can see the free pages going down to 0 but unfortunately I don't have a clue how to find a solution...
March 13, 2011 at 4:22 am
[font="Verdana"]
Can you share some information if the box is stand alone or a VM? SELECT @@VERSION would help.
Do you use OPENXML, extented procedures, sp_OA* etc in your environment? Have you set MAX Server memory on this box?
[/font]
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
March 14, 2011 at 2:49 am
Sankar Reddy (3/13/2011)
Can you share some information if the box is stand alone or a VM?
The box is a VM (2 Servers running on the same physical box).
SELECT @@VERSION would help.
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
Do you use OPENXML, extented procedures, sp_OA* etc in your environment? have you set MAX Server memory on this box?
No to all.
March 14, 2011 at 6:14 am
Hi,
Just to confirm the job always fails when re-building the same index...? How big is the index and how much memory do you have allocated to your server \ sql instance..?
March 14, 2011 at 8:27 am
[font="Verdana"]
Alexander,
I think we have identified the problem. SQL Server is memory hungry beast and doesn't play nice when you have 2 instances competing for memory if you don't set the Max Server memory.
Based on the usage and how much memory is available on the box, you should set the Max Server memory in both the boxes and that will resolve the issue.
The below articles will help on how to set them.
http://msdn.microsoft.com/en-us/library/ms178067.aspx
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
March 14, 2011 at 9:26 am
I think we have identified the problem. SQL Server is memory hungry beast and doesn't play nice when you have 2 instances competing for memory if you don't set the Max Server memory.
Are you sure? I have 2 VMs (each 8 GB RAM) running on one physical machine, but each VM has just one db instance.
Based on the usage and how much memory is available on the box, you should set the Max Server memory in both the boxes and that will resolve the issue.
The below articles will help on how to set them.
Thank you for the links. But I am not sure which one to follow:
MS recommends for the maxservermemory to sum stolen and reserved pages which in my case is about 380,000 pages (when the job is failing) i. e. a maxservermemory setting of about 3,000 MB.
The other links recommends a setting of 6,700 MB for 8 GB of physical RAM.
Which setting would you recommend?
Alexander
March 14, 2011 at 10:06 am
Let me first ask this. Have you enabled the TF 845 for the SQL Server 2008 R2 Std edition instances as well to use the Locked Pages in memory for Buffer pool? You may want to look at that first.
http://support.microsoft.com/kb/970070
Alexander G. (3/14/2011)
Are you sure? I have 2 VMs (each 8 GB RAM) running on one physical machine, but each VM has just one db instance.
Thanks for sharing the details and it helps us NOT to assume things as I did above and work towards a resolution. Please see my above comment on using the TF.
Thank you for the links. But I am not sure which one to follow:
MS recommends for the maxservermemory to sum stolen and reserved pages which in my case is about 380,000 pages (when the job is failing) i. e. a maxservermemory setting of about 3,000 MB.
The other links recommends a setting of 6,700 MB for 8 GB of physical RAM.
Which setting would you recommend?
Alexander
There are NO rules that fits for every enviornment. You should set a value that's reasonable for your environment and look at the patterns like PLE, Page File usage, memory usage and tweak them accordingly.
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
March 16, 2011 at 4:14 pm
Alexander G. (3/14/2011)
Are you sure? I have 2 VMs (each 8 GB RAM) running on one physical machine, but each VM has just one db instance.
how much memory does the host machine itself have?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 22, 2011 at 1:51 am
Hi the host has 32 GB RAM.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply