October 20, 2006 at 7:17 am
Hi,
I have transactional replication going through on SQL 2000 Server. The replication fails every now and then with errors that it cannot insert a duplicate row.
For starters, I have a job which periodically checks the replication agent status. If the status is stopped, the job will start the agent again. This works where the error received is due to timeouts and bad bandwidth.
The problem is that the above job will not be able to correct the replication where there is a duplicate row.
I would like to automate the correction of the replication job if the error is related to a duplicate row. I cannot find a table that reports the exact error that is shown when checking the replication monitor. If I can find the error, I can search for the id that has been duplicated and write some kind of procedure.
Does anyone have any ideas?
Thanks
Felicity
October 20, 2006 at 1:01 pm
Perhaps the most frequently used procedure is sp_browsereplcmds. but below are some more that will help you. I got this info from sqlservercentral.com. you can also add -skiperrors parameter to permit the Distribution Agent to skip certain errorsfor specific subscription, the individual command that causes this error is not applied to the Subscriber, but all other commands in the same transactions are applied.
Replication Related Table Name | Description |
Msrepl_errors | Contains rows with extended Distribution agent failure information. |
MSLogreader_history | Contains history rows for the Log Reader agents associated with the local distributor. This table is queried for populating the Agent History screen within Enterprise Manager. |
MSLogreader_agents | Contains one row for each Log Reader agent running at the local distributor. |
MSSubscriber_info | Contains one row for each publisher/subscriber pair that is being pushed subscriptions from the local distributor. |
MSSubscriber_schedule | Contains default transactional synchronization schedules for each publisher/subscriber pair. |
MSSubscriptions | Contains one row for each subscription served by the local distributor. |
MSSnapshot_history | Contains history rows for the Snapshot agents associated with the local distributor. |
MSSnapshot_agents | Contains one row for each Snapshot agent associated with the local distributor. |
MSPublications | Contains one row for each publication that is replicated by a publisher. |
MSPublication_access | Contains a row for each Microsoft SQL Server login that has access to the specific publication or publisher. |
MSRepl_transactions | Contains identifier and sequence number for each replicated transaction. |
MSArticles | Contains one row for each article being replicated by a publisher. |
MSPublisher_databases | Contains one row for each publisher/publisher database pair serviced by the local distributor. |
MSRepl_originators | Contains one row for each updatable subscriber from which the transaction originated. |
MSRepl_commands | Contains rows of replicated commands in binary format. |
MSDistribution_history | Contains history rows for the Distribution agents associated with the local distributor. |
In addition to these tables, replication can create additional tables in each publisher and subscriber database. These tables vary depending on type of replication you use.
I hope this will help.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply