Sql Server Error 1474 State 1hereiup

  1. Sql Server Error 1474
  2. Sql Server Error 1480
  3. Sql Error 1474
  4. Error 1474 Severity 16 State 1
1474-->State

This article helps you resolve the problem where mirrored databases are left in a Disconnected or In Recovery state.

1474

Goto services.msc and check the sql server is running under which account. Make sure that sql server and sql server agent services should run with same credentials. In the mirror database server you should do the same step as step 1. Give the same credentials as in principal server. CPU: Quad-Core X3440 CPU RAM: 16GB RAM Disk: 2x120GB SSD + 300GB SATA RAID: RAID 1 Bandwidth: Unmetered Windows 2016/2012: Free Monthly: $79.

Original product version: SQL Server
Original KB number: 2490051

Symptom

Consider the following scenario:

  • You have a computer that is running a secondary instance of Microsoft SQL Server 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.

Sql Server Error 1474

Method 1

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

  1. On the principal database, execute the following SQL script to stop the endpoint:

  2. Execute the following SQL script to restart the endpoint:

    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:

Method 2

Sql Server Error 1480

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

Sql error 1474

Sql Error 1474

What a coincidence. You understand mirroring from a text book sense, and I wrote the text book (Pro SQL Server 2008 Mirroring). :)

Error 1474 Severity 16 State 1

If the cause of the disconnection is fixed, mirroring may resume running on it's own with no intervention from you. If it doesn't resume on it's own, you should only need to issue the ALTER DATABASE ... SET PARTNER RESUME; command.

There is a scenario where simply resuming mirroring will not work. If you resume mirroring and it fails to connect, then the endpoint may have stopped. Database mirroring is built on top of service broker, and an in-flux of the same error repeatedly can result in the endpoint stopping (even though ti still says that it is in a running state in sys,database_mirroring_endpoints). In service broker terminology, this is called a 'poison message'. When service broker detects a poison message, it stops the queue. When this happens, you can usually fix it by changing the state of the endpoint on both partners to stopped and then back to running using the ALTER ENDPOINT command.

I have also seen a couple of scenarios where stopping/starting the endpoint did not get it restarted. In these cases, dropping and recreating the endpoints resolved the issue.

The error messages you asked about would be normal errors expected if the other partner had rebooted or had the SQL service restarted.