8

I have some Microsoft SQL server 2012 mirrors, without a witness in asynchronous mode, that are sporadically going into disconnected. I am running SQL Server 2012 11.0.5058. Looking at the logs on the mirror side I see

Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 1

There is a whole bunch of memoryclerk messages

Error: 802, Severity:17, state: 0.

There is insufficient memory available in the buffer pool.

Error:1454, Severity: 16, State: 1.

Database mirroring will be suspended. Server instance 'instance' encountered error 802, state 0, severity 17 when it was acting as a mirroring partner for database 'database'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.

Any help as to what to check to find out why the mirrors go into suspended and then disconnected following this chain of events would be appreciated. I can reestablish the mirrors after this and they work just fine for a couple of days and then it happens all over again.

John Wesley Gordon
  • 233
  • 1
  • 9
  • What is the max memory set to ? Also do you use Service broker by any chance ? – Kin Shah Apr 20 '15 at 15:14
  • On mirror and primary, 2147483647 MB. Not using Service Broker. – John Wesley Gordon Apr 20 '15 at 15:22
  • 2
    Ok .. thats one thing to make it non default - else SQL server is going to use up all the memory starving your OS. Can you set it to a sensible value ? As good starting point you can use this reference. Also, can you recheck your SQL Server build 11.0.5048 sounds incorrect ? – Kin Shah Apr 20 '15 at 15:36
  • Typo, it was 11.0.5058 – John Wesley Gordon Apr 20 '15 at 15:41
  • 1
    So SP2 RTM, which is a year and a half old - have you considered moving up to SP3 (and optionally CU1)? Don't know if it will fix your issue but there are a lot of other fixes in there that might be worthwhile. – Aaron Bertrand Jan 31 '16 at 03:28

2 Answers2

1

I experienced very similar issue in my previous company. Database miroring was periodically getting suspended with the same error as you are getting. Mirroring never ever reconnected on its own, but usually I was able to resume it using:

ALTER DATABASE <Database Name> SET PARTNER RESUME

After a few weeks we figured out that even though there was enought memory on the server and enough memory was allocated to the instance our workload was very unevenly distributed and sometimes 100% of the memory was used. Since most of the time we had enough memory we were not authorised to purchase more. I ended up increasing buffer pool extension file and placing it on one of the SSDs which happened to have enough empty space to accommodate it. The problem with mirroring never returned again. I cannot guarantee that the same will work for you, I am mainly sharing my experience.

QWE
  • 498
  • 7
  • 15
0

Few questions to ask and start troubleshooting
1.How many databases are you mirroring
2.How much RAM is available for sql server
3.What is the mirrored database size

Below are the rough estimates of mirroring requirements when mirroring is enabled

on Prinicipal:
4 MB per database for DBM send buffers plus 4 MB for log write buffers

On Mirror:
1 MB for log write buffers and 1 MB for log REDO Buffer

You also can check memory consumption per DB on mirror instance to see which database is the bottleneck and start troubleshooting

Memory utilization per database - SQL Server

Community
  • 1
TheGameiswar
  • 2,989
  • 4
  • 29
  • 50
  • I have 3 databases being mirrored. The Primary server has 16 GB of memory and the mirror has 20 GB memory. The biggest memory usage on the mirror right now is 3.8 MB for the mirrors. The mirror database has it's own primaries. The biggest primary is 9301 MB. The total of all databases on the mirror server is 9593.75 MB. This doesn't appear to be getting close to the 20 GB mark. The primary is even less, using only 6765 MB. – John Wesley Gordon Apr 20 '15 at 15:23