Buffer Pool Error 802 (no more pages can be stolen)

  • 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...

  • [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]

  • 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.

  • 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..?

  • [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

    http://sqlserverperformance.wordpress.com/2008/08/06/suggested-max-memory-settings-for-sql-server-20052008-2/%5B/font%5D

    [font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

  • 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.

    http://msdn.microsoft.com/en-us/library/ms178067.aspx

    http://sqlserverperformance.wordpress.com/2008/08/06/suggested-max-memory-settings-for-sql-server-20052008-2/%5B/font%5D

    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

  • 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]

  • 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" 😉

  • 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