3

We have bunch of SQL Server 2012 Standard and SQL Server 2014 Standard database instances running on Windows 2012, each have different storage and memory. We are getting:

Error Code: 802; There is insufficient memory available in the buffer pool.

from 3 database servers when we build large SQL Server indexes, and it's happening intermittently, once in 3 or 4 months. The same indexes run fine on servers with same version of SQL Server and only 1/3 of memory and CPU.

SQL Server 2012 Standard Servers

  1. Memory 110GB allocated out of 128 GB. Version 11.0.6 - We get reported the following error when we run heavy index:

    • Error Code: 802; There is insufficient memory available in the buffer pool.

  2. Memory 78GB allocated to SQL out of 96GB. Version 11.0.6 - We get reported the following error when we run heavy index:

    • Error Code: 802; There is insufficient memory available in the buffer pool.

  3. Memory allocated 18GB out of 24GB. VM. Version 11.0.5

    • No indexing error
  4. Memory 24GB out of 32GB. Physical. Version 11.0.5

    • No indexing error

SQL Server 2014 Standard Servers

  1. Memory 56GB out of 64GB physical memory. Version 12.0.5 - We get reported the following error when we run heavy index:

    • Error Code: 802; There is insufficient memory available in the buffer pool.

  2. Memory 18GB out 24GB - VM. Version 12.0.5.

    • No indexing error

closed as off-topic by Erik Darling, mustaccio, LowlyDBA, McNets, Sean Gallardy Oct 10 '17 at 18:26

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, LowlyDBA, McNets, Sean Gallardy
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    When are these index rebuilds happening, are other processes running? Are these servers hosting the same data/indexes or totally different data? If different, comparing them is not relevant. You should also upgrade your 2012 boxes to SP4 . – LowlyDBA Oct 9 '17 at 16:38
  • I'd look to see if there is any memory pressure on the server itself (windows) - especially if this is a virtual machine and not using locked pages or not having the memory reserved for the guest. I'd also check to see if there are any 3rd party applications gobbling up memory. – Sean Gallardy Oct 9 '17 at 17:44
  • no, the error reported in physical boxes. all these are dedicated DB servers and no other processes running. – Thiyagarajan Viswanathan Oct 9 '17 at 18:12
  • Not sure what your question is, but did you have a chance to read the error explanation in the manual and follow the troubleshooting steps suggested therein? – mustaccio Oct 9 '17 at 18:23
  • You note this happens intermittently, every 3-4 months, when building a large index. That would imply that, most of the time when building a large index on these servers, it's successful. Is this correct? – RDFozz Oct 9 '17 at 21:19
2

I don't think there's much mystery to the the errors occurring with SQL 2012 Standard as that edition only supports 64GB of memory. Set your max server memory to 64GB and this error should disappear for your SQL 2012 instances.

As for SQL 2014 Standard Edition, the max server memory limit increased to 128GB, so I'll echo Sean Gallardy's question in the comments and ask what else you're running on that VM or if you've over-provisioned the VM host by accident?

  • i do have same thought the SQL 2012 may be allocated with more memory than it can support. we dont run anything other sql SQL server on these servers. do you have any evidence about the behavior of SQL server when we allocate more memory that it support ? – Thiyagarajan Viswanathan Oct 9 '17 at 18:14
  • @ThiyagarajanViswanathan I've ran into that error numerous times running Express edition where I forgot to change the default max server memory setting. I expect the same behavior will also occur with Standard Edition. – John Eisbrener Oct 9 '17 at 18:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.