August 20, 2014 at 6:58 am
Hello,
Since about 2 months (in fact, since these databases are in production mode), we encounter a serious issue on 2 SQL 2012 SP1 instances, configured in mirror mode.
These 2 instances crash once a day (...at least), making the mirror switch, with the following error messages in the instance log :
Erreur : 701, Gravité : 17, État : 123.
There is insufficient system memory in resource pool 'internal' to run this query.
Failed allocate pages: FAIL_PAGE_ALLOCATION 1
Erreur : 17186, gravité : 16, état : 1. (paramètres : ). L'erreur est indiquée de manière laconique car un problème s'est produit lors de la mise en forme. Le traçage, le traçage d'événements pour Windows, les notifications, etc. sont ignorés.
This tends to go with a lack of physical memory on the servers.
However, each underlying server has 8 Gb of RAM. The SQL instances can consume up to 6 Gb (I have put this limit so as to preserve the OS, Windows Server 2012), and obviously, they always consume 6 Gb in the end. This is particularly strange since the user datas in the instances take only a few Mb.
We have installed CUP 2 for SQL 2012 SP1 (kb 2769594), which is made for correcting memory leaks such as those we encounter. But it did not solve the issue at all.
Any ideas ?
Thank you very much.
regards,
Cedric
August 20, 2014 at 8:17 am
Are they dedicated sql servers or is anything else running on them that is trying to use a ot of memory? have you tried setting a min memory setting for the sql instances of say 4gb to ensure the instance memory is not being consumed by other processes?
August 20, 2014 at 8:19 am
I'd open a support case for this since you have applied the CU that should have fixed this issue. It is unlikely you'll get an answer on a forum for a problem like this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2014 at 9:06 am
SQL has a feature to use all the memory in the Server.
If this server is not dedicated(this always hosts the APP or batch processes) then limit the maximum server memory to 80% to SQL server and remaining 20% for other application process.
August 20, 2014 at 9:17 am
I'm with Jack, I'd suggest getting in direct touch with Microsoft. A crash of the instance is not something simply changing a setting or tuning a query is going to address. Just because you ran out of memory, the server shouldn't go down.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 21, 2014 at 3:54 am
Thank you for your answers.
The issue is getting worse from day to day... since yesterday, the lead instance of the mirror crashes (forcing the mirror to switch) and does not restart, leaving the underlying server with 100% CPU consumed.
Obviously, CUP 2 has made the issue more critical than before 🙁
We have opened a call at Microsoft's support.
regards,
Cedric
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply