locked
Error 8474 State 11 - Old Witness Server RRS feed

  • Question

  • Hoping someone can offer a bit of advice.

    We have a SQL server which used to be a witness, the server it was a witness to is not longer in use. Now since a couple of weeks back we started getting these in the event log at the same time every day - 4 in a row.

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

    But we have no idea why these have started now and what they mean as there is no main or mirror server connected and in the mirroring tables (mirroring / endpoints/witnesses/connections) there is nothing there. Any help would be great as not sure if something is using the same ports and causing this or an old reference to it being a mirroring witness.

    Thanks in advance.

    Nick

    Monday, August 22, 2016 3:19 PM

Answers

  • Hi all,

    Thanks for the help and advice, I think I have solved (or found the issue), as mentioned this was an old witness server to the previous CRM SQL server which is switched off. This server is a backupexec server and I was connecting to that DB rather than the local DB.

    Once connected to the local DB I could see an endpoint setup for database_mirroring which was started. I believe it was stopped (wasnt at the company when all the mirroring was in use) but recent windows / sql server updates / server reboots might have started this endpoint again which then causing these errors.

    I have stopped the DATABASE_MIRRORING endpoint and will monitor for the day to see if the error happens, if it does not then I will see about removing the endpoint to stop it starting up again and causing the errors.

    Nick

    Thursday, August 25, 2016 10:11 AM

All replies

  • 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
    • http://dba.stackexchange.com/questions/86049/mirroring-sql-error-log-error-8474-state-11

    Please click Mark As Answer if my post helped.

    Monday, August 22, 2016 4:27 PM
  • Hi,

    This might be other reasons as well-

    1.Issues with your endpoints ports used for communication b/w mirror & principle over the network,
    you can try with restarting endpoints how ever the issue might can repeat under some circumstances like as below :-

    1.Your port which is configured for mirroring might be used by some other processes(usally it is not designed in such way but worse condition) --this you need to check with windows/network team.

    2.The ports in the servers get exhausted.

    3.Verify with commands to see are the endpoints is ok.

    SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, 
           t.port, e.is_encryption_enabled, e.encryption_algorithm_desc, 
           e.connection_auth_desc 
    FROM   sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
    ON     e.endpoint_id = t.endpoint_id;
    


    4.Try telnet with the DB port from both the servers to see it has no issues and connect with db server,port from the other sql server which is same domain to see they are ok.

    5.Can you also check in the sql errorlog both principle & mirror servers apart from those errors -what else it is logged during the same time(before & after)?.


    Regards, S_NO "_"

    Tuesday, August 23, 2016 3:20 AM
  • Hi all,

    Thanks for the help and advice, I think I have solved (or found the issue), as mentioned this was an old witness server to the previous CRM SQL server which is switched off. This server is a backupexec server and I was connecting to that DB rather than the local DB.

    Once connected to the local DB I could see an endpoint setup for database_mirroring which was started. I believe it was stopped (wasnt at the company when all the mirroring was in use) but recent windows / sql server updates / server reboots might have started this endpoint again which then causing these errors.

    I have stopped the DATABASE_MIRRORING endpoint and will monitor for the day to see if the error happens, if it does not then I will see about removing the endpoint to stop it starting up again and causing the errors.

    Nick

    Thursday, August 25, 2016 10:11 AM
  • Hi all,

    Thanks for the help and advice, I think I have solved (or found the issue), as mentioned this was an old witness server to the previous CRM SQL server which is switched off. This server is a backupexec server and I was connecting to that DB rather than the local DB.

    Once connected to the local DB I could see an endpoint setup for database_mirroring which was started. I believe it was stopped (wasnt at the company when all the mirroring was in use) but recent windows / sql server updates / server reboots might have started this endpoint again which then causing these errors.

    I have stopped the DATABASE_MIRRORING endpoint and will monitor for the day to see if the error happens, if it does not then I will see about removing the endpoint to stop it starting up again and causing the errors.

    Nick

    Thanks , I will get back to you on this when I get quite free time.. need to understand.

    Regards, S_NO "_"

    Friday, August 26, 2016 7:13 AM