Mirrored databases are disconnected after you restart the database mirror in SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

Symptom

Consider the following scenario:

You have a computer that is running a secondary instance of Microsoft SQL Server 2005, Microsoft SQL Server 2008, or Microsoft SQL Server 2008 R2 in a two-server database mirror.
CPU usage reaches 100 percent on the computer, and you cannot stop the SQL Server service by using SQL Server Management Tools.
You end the process of the SQL Server secondary instance by using Task Manager.
You restart the secondary instance of SQL Server.

In this scenario, all mirrored databases are in a "Disconnected" or "In Recovery" state. Additionally, an error message that resembles the following is logged in the SQL Server error log for each database:

Bypassing recovery for database 'Database Name' because it is marked as an inaccessible database mirroring database. A problem exists with the mirroring session. The session either lacks a quorum or the communications links are broken because of problems with links, endpoint configuration, or permissions (for the server account or security certificate). To gain access to the database, figure out what has changed in the session configuration and undo the change.

Cause

This issue occurs because of problems in the SQL Server database mirroring endpoints.

Resolution

To resolve this issue, use the following methods. If the first method does not resolve the issue, use the second method.

Method 1

Recycle the endpoint on the database mirror. To do this, follow these steps:

On the principal database, execute the following SQL script to stop the endpoint:
```
ALTER ENDPOINT <Endpoint Name> STATE=STOPPED
```
Execute the following SQL script to restart the endpoint:
```
ALTER ENDPOINT <Endpoint Name> STATE=STARTED
```

Note If communication between the endpoints does not restart after you execute the scripts, execute the scripts on the database mirror. However, the database may enter a "Suspended" state after you do this. If this issue occurs, execute the following SQL script:

ALTER DATABASE <Database Name> SET PARTNER RESUME

Method 2

Delete and re-create the database mirroring endpoints on both servers.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Properties

Article ID: 2490051 - Last Review: Jan 30, 2013 - Revision: 1

Applies to

Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Web, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Web, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2005 Evaluation Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition

Feedback

Was this information helpful?