Database Mirroring Time-Out Mechanism

Error: 1479, Severity: 16, State: 2

The mirroring connection to “TCP://ComputerName:PortNumber” has timed out for database “DatabaseName” after 10 seconds without a response. Check the service and network connections.

Twins
Are you Synchronous or Asynchronous? If you do not need auto-failover, increasing the timeout may prevent false positives that may occur during times of heavy load. If you set the timeout to 60 seconds, then failures that resolve within that time frame won’t even bother the existing setup.

The Mirroring Time-Out Mechanism
Because soft errors are not detectable directly by a server instance, a soft error could potentially cause a server instance to wait indefinitely. To prevent this, database mirroring implements its own time-out mechanism, based on each server instance in a mirroring session sending out a ping on each open connection at a fixed interval.
To keep a connection open, a server instance must receive a ping on that connection in the time-out period defined, plus the time that is required to send one more ping. Receiving a ping during the time-out period indicates that the connection is still open and that the server instances are communicating over it. On receiving a ping, a server instance resets its time-out counter on that connection. If no ping is received on a connection during the time-out period, a server instance considers the connection to have timed out. The server instance closes the timed-out connection and handles the time-out event according to the state and operating mode of the session. Even if the other server is actually proceeding correctly, a time-out is considered a failure. If the time-out value for a session is too short for the regular responsiveness of either partner, false failures can occur. A false failure occurs when one server instance successfully contacts another whose response time is so slow that its pings are not received before the time-out period expires. In high-performance mode sessions, the time-out period is always 10 seconds. This is generally enough to avoid false failures. In high-safety mode sessions, the default time-out period is 10 seconds, but you can change the duration. To avoid false failures, don’t set your timeout to less than 10 seconds.

If you notice that you get timeouts during certain times of the day, or when one of your SQL Agent Jobs run, you could write a script to run right before that time of day, or right before that job executes, that changes the timeout to a larger number, then have another script that runs at the end of the time period, or when the job completes, that changes it back.

If you experience these types of resource hits that cause mirroring to time out, then you should get to the root cause of your performance issue. Increasing the mirroring timeout is just a temporary band aide.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s