SQL Server Performance

Error 14040 - the server is already a subscriber.

Discussion in 'Performance Tuning for SQL Server Replication' started by peti, Oct 24, 2006.

  1. peti New Member

    Hello all,<br /><br />I have a new transactional publication. When I try to push a subscription from this publication to another server I get this error:<br />"Error 14040: the server is already a subscriber."<br /><br />The respective server is present in the Subscribers list (Publisher and Distributor properties -- Subscribers tab) and it's not enabled. I've got the same error message when I tried to enable it.<br /><br />This is the outcome of "select sub, srvname from sysservers where srvname='Myserver'":<br /><br />sub srvname <br />---- ------------------- <br />1 Myserver<br /><br />Will this solve the problem?<br /><br />USE &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />ublication database name&gt;<br />GO<br />EXEC sp_dropsubscription @publication = N'&lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />ublication name&gt;', @article = N'all', @subscriber = N'all', @destination_db = N'all'<br /><br />Thank you,<br />peti<br />
  2. satya Moderator

    Basically appears that the distribution database has been corrupted, try to perfom the following:

    1. From Query Analyzer, run this SELECT statement: SELECT * from msdb..msdistributiondbs
    2. If a row is returned delete it.
    3. Then, reconfigure replication. From Enterprise Manager, go to Tools->Replication-> Configure publishers, subscribers,and distribution.
    4. Run Step 3 again, and you should then be able to create the distribution

    If that doesn't work, you may need to remove replication from the server and reestablish replication.

    Please be aware that these steps will remove replication from the server. If you are uncertain about how to re-establish replication, refer to books online for more information.

    1. From Query Analyzer, run the following commands:
    sp_configure 'allow',1 go
    reconfigure with override go
    DELETE master..sysservers WHERE srvname = 'repl_distributor'

    2. Re-run the replication wizard and re-establish replication on the server.



    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. knightEknight New Member

    In addition to reconfiguring replication on the server, you may need to delete entries from sysservers and sysxlogins that correspond to the subscriber:


    declare @srvid int

    select @srvid = srvid from master..sysservers where srvname = 'YourSubscriberDB'

    delete from master..sysservers where srvid = @srvid

    delete from master..sysxlogins where srvid = @srvid

Share This Page