Stopping replication

  • A user wrongly modified a source table. The errors of many rows were being replicated out. What is the best way to stop the replication on that one publication? Then, assuming the error is fixed, start replication again for that one table?

    The user found it difficult to stop the replication, which was around 1 million records.

    Thanks

    Graham

    Graham Okely B App Sc
    Senior Database Administrator

  • I assume that you want to stop replicating a single table within a publication because there are a lot of changes made. To do that, drop all subscribers to the table (using sproc sp_dropsubscription) and then drop the article that contains that table from the publication (using sproc sp_droparticle).

    When you have sorted out the issue, add the article back in to the publication and add the subscribers back in. Depending on what you have done and any other changes made to the data in the table on your publisher, you may need to send another snapshot to the subscriber (for this one article).

  • Can't you correct the records and replicate the changes.

    Or can't you stop the distribution and delete the commands from the distribution database ?

    Graeme

  • Yes you can. Stop the agent affecting that replicated publication / article. Then you can use the following queries to find what replicated transactions you need to delete. Please note though that this will delete all transactions for that article not just the ones that were done by that one statement.

    This query will give you the publisher_database_id and article_id based on the table that you put in the parameter.

    /*

    The following query, when run from the distributor server will return useful ID

    information given a publication that is failing or a specific table.

    */

    USE DISTRIBUTION

    DECLARE @PARM VARCHAR(50)

    SET @PARM = 'YourTable'

    SELECT P.PUBLICATION,

    DB.PUBLISHER_DB,

    DB.ID PUBLISHER_DATABASE_ID,

    A.ARTICLE,

    A.ARTICLE_ID

    FROM MSARTICLES A

    JOIN MSPUBLICATIONS P

    ON P.PUBLICATION_ID = A.PUBLICATION_ID

    JOIN MSPUBLISHER_DATABASES DB

    ON DB.PUBLISHER_DB = P.PUBLISHER_DB

    WHERE A.ARTICLE = @PARM

    OR P.PUBLICATION = @PARM

    OR CONVERT(VARCHAR(20),A.ARTICLE_ID) = @PARM

    OR CONVERT(VARCHAR(20),P.PUBLICATION_ID) = @PARM

    The next query will get you the pertinent information that you need to use to in the delete statement.

    /*

    Given the article_id and publisher_database_id, one can query for transactions that

    are waiting to be replicated:

    */

    SELECT *

    FROM MSREPL_TRANSACTIONS

    WHERE XACT_SEQNO IN (SELECT XACT_SEQNO

    FROM MSREPL_COMMANDS

    WHERE ARTICLE_ID = 585 --Insert your Article_ID here

    AND PUBLISHER_DATABASE_ID = 4) --Insert your publisher database id here.

    SELECT *

    FROM MSREPL_COMMANDS

    WHERE ARTICLE_ID = 585 --same as above

    AND PUBLISHER_DATABASE_ID = 4 --same as above

    /*

    With cautition, all transactions for an article can be deleted from the queue with the

    following queries:

    */

    DELETE FROM MSREPL_TRANSACTIONS

    WHERE XACT_SEQNO IN (SELECT XACT_SEQNO

    FROM MSREPL_COMMANDS

    WHERE ARTICLE_ID = 235 --same as above

    AND PUBLISHER_DATABASE_ID = 5)--same as above

    DELETE FROM MSREPL_COMMANDS

    WHERE ARTICLE_ID = 235 --same as above

    AND PUBLISHER_DATABASE_ID = 5 --same as above

    These have to be run on the distributor in the distribution database.

    Hope this helps.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply