[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

 Database Mirroring Disconnected
 

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
Tips
Troubleshooting
Tuning

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.