October 17, 2017 at 10:36 am
I have a SQL Server 2016 principal and SQL Server 2016 mirror. Mirroring on one database has been running fine until today.
We are using High performance (asynchronous) mirroring.
Because the principal and mirror are connected by a slow connection I have the timeout set to 60 seconds.
When I use the Database Mirroring Monitor to watch what is going on I often see the mirroring state for both Principal and Mirror as Synchronizing. Sometimes one or both will show a state of Disconnected. Most of the time the Unsent log and the Unrestored log show 0 and usually when this happens the state switches to synchronized.
Sometimes, like right now I see a value in Unrestored log. In this case it is 1041821 KB with a time to restore of 00:00:19.
The oldest unsent transaction reads 11:52:41 and my current time is 12:22 PM.
I used profiler on both the Principal and the Mirror and see no transactions on the Principal nor the Mirror unless I execute something on the Principal.
From what I have been able to observe so far it looks like the unrestored log is getting replayed on the mirror until it is down to 0 and there are no unsent logs pending and so the state should switch from Synchronizing to Synchronized but it is not - it stays as Synchronized. I have a process that records mirroring status which I have running as fast as it will go which is getting data from the SQL Server mirror table and saving it in an archive table. So I am recording the status about every minute. Then while unsent log and unrestored log are both 0 in the Database Mirroring Monitor I start profiler on the principal. After a few minutes the unrestored log goes from 0 to about 500,000KB while profiler still shows no transactions ran on the principal in the database that is mirrored.
I have tried stopping and restarting the mirror end points but that doesn't help. I have paused and resumed mirroring and that doesn't help. I've stopped and restarted the mirror SQL Server and that didn't help. I can't stop and restart the principal during business hours so don't know if that would help or not.
I hope I have provided enough information for you to understand the problem.
My question is has anyone seen this before? Or does anyone have ideas on how to fix it?
Also how can there be unrestored log's if the unrestored log was 0 and there were no transactions on the principal?
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 19, 2017 at 12:34 pm
Yesterday, I removed mirroring for this database and set it up again. That solved the problem.
Unfortunately, it doesn't tell me what caused the problem so that I can avoid it in the future. I am still interested if anyone has ever seen something like this before and/or any thoughts on the cause or another way to solve the problem without completely redoing mirroring.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 19, 2017 at 3:16 pm
Robert W Marda - Thursday, October 19, 2017 12:34 PMYesterday, I removed mirroring for this database and set it up again. That solved the problem.Unfortunately, it doesn't tell me what caused the problem so that I can avoid it in the future. I am still interested if anyone has ever seen something like this before and/or any thoughts on the cause or another way to solve the problem without completely redoing mirroring.
Once but never figured anything out. Unsent and unrestored were at 0. It was fine after a pause(suspend) and resume, almost like it needed to be kick started.
I found some of the same approaches you tried when I was searching and trying to figure it out what was going on. Not too many ideas other than possibly something causing an uncommitted transaction on the mirror.
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply