July 27, 2008 at 8:30 pm
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
July 27, 2008 at 11:41 pm
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).
September 2, 2008 at 3:04 pm
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
September 2, 2008 at 3:18 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply