Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
raguyazhin
Posting Yak Master
105 Posts |
 Posted - 2011-03-10 : 03:45:43
|
| In Transactional Replication following Error Message was occurred.How To Solve the problem with any data loss in publication Database. Error messages:Violation of PRIMARY KEY constraint 'PK_testtable'. Cannot insert duplicate key in object 'dbo.testtable'. (Source: MSSQLServer, Error number: 2627)Get help: http://help/2627Violation of PRIMARY KEY constraint 'PK_testtable'. Cannot insert duplicate key in object 'dbo.testtable'. (Source: MSSQLServer, Error number: 2627)Get help: http://help/2627--Ragu ThangavelJunier Sql Server DBA |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-10 : 08:45:08
|
| Sounds like you're allowing inserts at the subscriber. Need to treat the subscriber as read only.Is the PK in question over an identity column? If so, (1) reseed the identity column at the subscriber (2) stop inserting at the subscriber. Perform all write operations only at the publisher. |
 |
|
|
raguyazhin
Posting Yak Master
105 Posts |
Posted - 2011-03-10 : 23:34:27
|
quote:
Originally posted by russell
Sounds like you're allowing inserts at the subscriber. Need to treat the subscriber as read only.Is the PK in question over an identity column? If so, (1) reseed the identity column at the subscriber (2) stop inserting at the subscriber. Perform all write operations only at the publisher.
Thanks, But Now I want clear this problem so what steps shall i taken?like Truncate the table from subscriber.--Ragu ThangavelJunier Sql Server DBA |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-11 : 15:07:27
|
| Do you know which are the offenings records?You may need to re-snapshot, but if you've identified the problem, you might be able to change the ids of the existing records. Careful though of other tables reference them.If you haven't identified the offending records, probably should just go ahead and run the snapshot agent -- which you may end up needing to do anyway. |
|
|
|
raguyazhin
Posting Yak Master
105 Posts |
Posted - 2011-03-12 : 00:02:46
|
quote:
Originally posted by russell
Do you know which are the offenings records?You may need to re-snapshot, but if you've identified the problem, you might be able to change the ids of the existing records. Careful though of other tables reference them.If you haven't identified the offending records, probably should just go ahead and run the snapshot agent -- which you may end up needing to do anyway.
Your reply was useful for me.but Our database size was 500 GB then snapshot is a very painful.so that position what shall we do?--Ragu ThangavelJunier Sql Server DBA |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-12 : 10:12:36
|
| Did the entire subscription fail?Is the subscriber in the same data center?Might try Initializing a Transactional Subscription Without a Snapshot but this, of course, means recreating the subscription and publication.You might also consider changing the distribution agent properties to ignore errors. If you do so, you'll want to validate subscriptions on a regular basis (I have SQL Agent jobs that do this). Ignoring PK errors WILL get the subscriber out of synch eventually.I don't like publications that are too big to distribute the snapshot comfortably in my maintenance windows (which are small). Instead, I'll create several smaller publications in some cases. True, I don't like having multiple log reader agents against the same T-Log, but it in some cases, it's an acceptable tradeoff.Let me reiterate that it is important not to perform write operations, especially inserts, at the subscriber. You should treat subscribers as read only. You can't make the subscriber db read only because then distribution would fail, but you need to make sure your application code isn't writing to the subscriber. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2011-03-16 : 19:41:59
|
| If its just one table, you can drop it from publication using sp_Droparticle and add it back using sp_Addarticle.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
|
|
|
|
PK Violation in Transactional Replication