September 28, 2016 at 6:23 am
Hi
Today I have below error in SQL Server Logs for many times during the day:
The mirroring connection to "TCP://ServerName:PortNum" has timed out for database "DBName" after 10 seconds without a response. Check the service and network connections.
Database mirroring is inactive for database 'DBName'. This is an informational message only. No user action is required.
Database mirroring is active with database 'DBName' as the principal copy. This is an informational message only. No user action is required.
The operating mode for mirroring is ,High safety without automatic failover(synchronous).
The problem is that we have insert time out exactly at the the times that mirroring connection has timed out.
Can mirroring has impact on principal database?I mean when the mirroring is inactive for a database,it just becomes disconnected or the transaction on principal server are involved?
September 28, 2016 at 7:05 am
High Safety Mode -
After synchronization finishes, every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction's log to disk. Note the wait for this message increases the latency of the transaction.
Q: Can mirroring has impact on principal database?
I mean when the mirroring is inactive for a database, it just becomes disconnected or the transaction on principal server are involved?
A: It is not supposed to, but, definitely the other way around, meaning a long transaction on the principal affects the secondary. I would pause the mirroring at the exact time of your issue to prove that the mirroring is not the cause:
Example -
ALTER DATABASE AdventureWorks2012 SET PARTNER SUSPEND;
ALTER DATABASE AdventureWorks2012 SET PARTNER RESUME;
September 28, 2016 at 7:25 am
RVSC48 (9/28/2016)
High Safety Mode -After synchronization finishes, every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction's log to disk. Note the wait for this message increases the latency of the transaction.
You mean the principal server waits for the message from the mirror server,so if it dose not receive a message,the transactions may get timeout.
How long it waits for the message?
September 28, 2016 at 8:17 am
Correct,
The time required for synchronization depends essentially on how far the mirror database was behind the principal database at the start of the session (measured by the number of log records initially received from the principal server), the work load on the principal database, and the speed of the mirror system. After a session is synchronized, the hardened log that has yet to be redone on the mirror database remains in the redo queue.
September 29, 2016 at 12:04 am
So how long does the principal wait for the message from the mirror server?Can I manually decrease that time?
September 29, 2016 at 6:13 am
The only thing you could do is try and speed up the mirroring process overall. Most of that is dictated by the network speed between the primary and secondary. With Log Shipping, you do have more control over the frequency of restoration, but with mirroring, this is not the case.
October 3, 2016 at 2:36 am
Thanks a lot 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply