Database Mirror TimeOut

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.”

 

 

 

database mirroring timeout message

 

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

SELECT Mirroring_Connection_Timeout

FROM sys.database_mirroring

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

Leave a Reply

Your email address will not be published. Required fields are marked *