SQL Server 2005 – Resolve Mirroring SQL Error Log 8474, State 11

error logmirroringsql-server-2005

I'm getting these errors in SQL error logs:

An error occurred in a Service Broker/Database Mirroring transport connection endpoint,
Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')
Error: 9642, Severity: 16, State: 3.

Principal and mirror servers are in synchronized state. They have been in synchronized state for some months now, but I keep getting these error messages, I checked the permissions and everything is like its supposed to. Timing on both the servers is the same.

How can I stop these error messages. Thanks for your help.

Best Answer

The mirror state is likely confused at this point and should not be trusted. cherish.j.wang posted the following at: http://www.sqlservercentral.com/Forums/Topic1237273-1549-1.aspx

use master
GO
alter endpoint endpoint_name state = stopped;
GO
alter endpoint endpoint_name state = started;
GO

In that same discussion Robert Davis (SQL Soldier) agreed that would resolve the problem for the moment. But he also pointed out that if you use the fully qualified domain name of the servers you are less likely to run into a name resolution error.

UPDATE: When you reset the endpoints, it will take the database offline for the duration of the restart. However, it will not break the mirror. The mirroring will stop running when the endpoint is stopped, but it will resume once the endpoint is again started.

The MSDN article: http://support.microsoft.com/kb/2490051 suggests.

Run these commands on the primary
If it does not resolve the problem, run the commands on the mirror.
If the database becomes SUSPENDED run ALTER DATABASE <Database Name> SET PARTNER RESUME

Related Solutions

Sql-server – Mirroring problems after removing domain

After days of searching, I found the answer 10 minutes after this post. The problem was that the admin account did not have read/write rights on c:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ . After changing that I also had to recreate the master keys and certificates to make it work.

Sql-server – Mirroring – Server network address cannot be reached

Try some basic connectivity tests.

Verify that 5022, 5023 and 5024 are listening.
Verify that the server name you are using is correct.

From the command line :

netstat -an

enter image description here

On my server, you can see that 5022 is listening.

Next make sure that you can connect to those ports via telnet

telnet fully-qualified-server-name 5022

As it mentions in the Note section of the Mirroring Properties GUI, just below the witness field, the server names have to be fully qualified tcp addresses.

enter image description here

You should just see a black screen. In this example I chose a name that would cause a connection failure. If you see "Could not open connection", then the server(s) defined as mirror, principal and witness aren't reachable or you are not using the right name.

The telnet client can be added under Features in Windows 2008.

In Windows 2008, when you right click on Computer, you can see the full computer name. You should be able to ping it as well from the command line. ex: ping myservername

Update

Please run the following queries on each SQL Server instance and put the results in your question. Many of these troubleshooting tips come from: http://msdn.microsoft.com/en-us/library/ms189127.aspx

Show tcp endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;

Display status of mirroring endpoints

SELECT state_desc FROM sys.database_mirroring_endpoints;

Check that the ROLE is correct

SELECT role FROM sys.database_mirroring_endpoints;

Display permissions to endpoints

SELECT EP.name, SP.STATE, 
   CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) 
      AS GRANTOR, 
   SP.TYPE AS PERMISSION,
   CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) 
      AS GRANTEE 
   FROM sys.server_permissions SP , sys.endpoints EP
   WHERE SP.major_id = EP.endpoint_id
   ORDER BY Permission,grantor, grantee; 
GO

The login for the service account from the other server instance requires CONNECT permission. Make sure that the login from the other server has CONNECT permission. To determine who has CONNECT permission for an endpoint, on each server instance use the following Transact-SQL statement.

Example output:

name    STATE   GRANTOR PERMISSION  GRANTEE
TSQL Local Machine  G   sqladmin    CO      public
TSQL Named Pipes    G   sqladmin    CO      public
TSQL Default TCP    G   sqladmin    CO      public
TSQL Default VIA    G   sqladmin    CO      public
Mirroring   G   SERVERNAME\Grantor  CO      SERVERNAME\Grantee

Grantor is the account that assigned (CO) connect permission, Grantee is the account that has connect permission

From the command line run ipconfig /all and note what Host Name returns.

Best Answer

Related Solutions

Sql-server – Mirroring problems after removing domain

Sql-server – Mirroring – Server network address cannot be reached

Related Question