Looking for a solution to the error ‘The mirroring connection to has timed out’? We can help you.
The failover mechanism within database mirroring is designed in such a way that the instances send across a ping within each other to understand if they are alive.
At Bobcares, we often get requests from our customers to fix the connection timed out error as part of our Server Management Services.
Today, we’ll see how our Support Engineers fix this error quickly.
What causes mirroring connection to has timed out error to occur?
The error message ‘Mirroring connection to has timed out’ relates to Database Mirroring failover procedure.
With the High Availability mode of synchronous Database mirroring with automatic failover there are three servers that are involved. They are Principal, Mirror, and Witness.
The three servers constantly ping each other. If the Principal instance didn’t receive any responses to “Ping” for 10 seconds from Witness then it shows the below error.
Common reasons of pinging response failed
- Slow network connection or network reliability
- Hardware/SAN/VM failure
- Geographically dispersed mirror partners
So to avoid the accidental database failover we increase the Partner Time Out value.
What is Partner timeout?
Partner timeout is the maximum period of time a server instance waits to get a ping message from another SQL Server. The instance in the mirroring session before considering that other instance to be disconnected. We can define the timeout option only on the principal server and specify the timeout period in seconds.
If we set the timeout value between 0 and 4 seconds it automatically set the timeout to 5 second. The default time period is 10 seconds.
How we fix the mirroring connection to has timed out error?
The default mirroring time out value is 10 seconds and the error is due to the low partner timeout value. Therefore, we increase the default timeout value.
1. We open the database with SQL Server Management Studio.
2. Then we open a new SQL query windows and execute the following statement.
ALTER DATABASE <dbname> SET PARTNER TIMEOUT 20We check the Timeout value by the below query.
SELECT Mirroring_Connection_Timeout
FROM sys.database_mirroring
WHERE database_id = db_id(‘<dbname>‘)
[Need more help to fix this error?- We’ll help you.]
Conclusion
In short, the mirroring connection timeout error occurs due to the partner timeout value. We saw that our Support Engineers fix the error by increasing the partner timeout value.
0 Comments