Database Mirroring Disconnected.

Applies to: SQL Server 2012 Enterprise Edition.
 

Problem Description.
 

One organization where I provide remote DBA services has a database mirror established between two sites on separate cities over the Internet. They call me to reconnect the Database Mirroring because they were seeing a message saying "disconnected" on the principal server. On the SQL Server Error Log I found the following error messages:

Date 4/21/2016 8:57:24 PM
Log SQL Server (Current - 4/15/2016 1:59:00 AM)

Source spid37s

Message
Database mirroring connection error 4 'An error occurred while receiving data: '121(The semaphore timeout period has expired.)'.' for 'TCP://DB3:5023'

Date 4/21/2016 8:56:48 PM
Log SQL Server (Current - 4/15/2016 1:59:00 AM)

Source spid38s

Message
The mirroring connection to "TCP://DatabaseServer3:5023" has timed out for database "XXXX" after 10 seconds without a response. Check the service and network connections

 

Solution.

Execute the following query to identify the name of the endpoint on the principal server.

SELECT * FROM sys.endpoints


Once you have the name of the endpoint, stop the endpoint with the following statement.


ALTER ENDPOINT EndpointName STATE=STOPPED


Start the endpoint again.

ALTER ENDPOINT EndpointName STATE=STARTED

If after recycling the enpoint the database has a "Suspended" state, please run the following statement:


ALTER DATABASE DatabaseName SET PARTNER RESUME


If the procedure explained above does not work, then proceed to delete and re-create the database mirroring endpoints on both servers.


 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.