Here is SQL Server Error Log Message I detected on one of SQL Server 2008R2
“The mirroring connection to “TCP://server_name:5022″ has timed out for database “<dbname> ” after 10 seconds without a response. Check the service and network connections.”
The message is related to Database Mirroring failover procedure.
With the High Availability mode of synchronous Database mirroring with automatic failover there are three servers that are involved: Principal, Mirror and Witness.
The three servers constantly ping each other, forming a quorum, and if one of the machines is not available the other machines determine how to handle the failover.
In this particular instance the Principal instance didn’t not receive any responses to “Ping” for 10 seconds from Witness, as a result the failover was initiated after 10 sec without a response. 10 sec is a default Partner Time out settings.
Most common reasons why the pinging response failed
- slow network connection or network reliability
- geographically dispersed mirror partners
- hardware/SAN/Vm failure
What can be done to avoid accidental database failover?
Increase Partner Time Out value.
What is Partner timeout?
- The maximum period of time a server instance waits to get a “ping” message from another SQL Server instance in the mirroring session before considering that other instance to be disconnected
- The TIMEOUT option only can be defined on the principal server
- Specifies the time-out period in seconds
- Option can not be set through the GUI
- The default time period is 10 seconds.
- If you set it for anything between 0 and 4 seconds it will automatically set the timeout to 5 seconds. If you set it for 5 seconds or greater it will be set to that value.
- Number specified must be an integer
To change Partner Timeout to 20 sec
ALTER DATABASE <dbname> SET PARTNER TIMEOUT 20
Other Useful queries while troubleshooting Database Mirroring
Checking Timeout value
WHERE database_id = db_id(‘<dbname> ‘)
Query to check the associated ports with DB Mirroring
SELECT type_desc, port FROM sys.tcp_endpoints where type_desc like ‘%MIRROR%’;
Query to check the state of the DB Mirroring
SELECT state_desc FROM sys.database_mirroring_endpoints