5

We've been having an issue with our SQL server lately. It seems that the server will run out of memory and then crash. For some reason, the sql service wouldn't restart. I would have to go and manually restart it and then it's fine.

I am still trying to figure out the memory issue. We are using SQL Server 2012 SP1 Enterprise Edition. This is on a Windows 2012 Server. I saw alot of 701 out of memory error on the server right before it crashed and I'm still trying to figure out what is causing the memory error.

Why wouldn't the SQL Service be restarted automatically? Is it because of the memory issue?

Also, server has 20gb of memory and the max server memory is set to 16.5gb. I have noticed that the MEMORYCLERK_SQLOPTIMIZER is really high, around 11.5 gb right before the server crashes.

Anyone know why that would be so high? Is that what's causing the memory errors?

Any help will be appreciated. Thanks.

  • Is this on a VM? Whats your max memory setting and how much RAM does the server have? – James Anderson Jun 9 '15 at 19:03
  • Yes, this is running on a VM. The max server memory is at 16.5gb and the server has 20gb of RAM. – kaitlyn Jun 9 '15 at 19:04
  • VM Ware or Hyper V? How much RAM does the virtual host have available? – James Anderson Jun 9 '15 at 19:20
  • We are using VMWare 5.1 and the host has 96gb. – kaitlyn Jun 9 '15 at 19:28
  • 1
    Well it doesnt seem to be anything obvious (at least not to me). I would suggsst a review of any non default settings in SQL Server and the OS, trace flags, paging settings, dynamic memory settiings on the VM. Can you move the VM to another host to rule out other VMs causing the issue? Does it crash during high loads (no of connections) or is the load not connected? – James Anderson Jun 9 '15 at 21:46
1

You are running into a known bug - There is a fix for it but update to SP2 rather than installing the hotfix.

The errorlog contains SQL Server is terminating a system or background task HADR Task due to errors in starting up the task (setup state 1). and you are using availability groups. The memory leak is in the MEMORYCLERK_SQLLOGPOOL which is used for transaction log activity.

  • But i have checked and i didn't see MemoryClerk_SQLLOGPOOL very high so I didn't think this was the issue. I will apply SP2 though. – kaitlyn Jun 10 '15 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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