SQL Server Performance Forum – Threads Archive
Replication missing records…opinions needed.
Hi. I’m having a problem with my replication. It seems that records have gone missing on my subscribers, which leads to failure in the replication. Config:3 machines – primary (A), and two secondary (B,C).
Transactional replication – no imidiate update, no allow queued updating, allow anon subscriptions. machine A – Publisher/Distributor
machine B – Subscriber
manicne C – Subscriber Publication has no filtering, identity columns are flagged as Not for Replication, and uses stored procedures. Subscription is anonymous pull, and seems to have no issues with security (connects just fine). Distribution is set to fire off every five minutes from machine B and every fifteen from machine C. The system it replicates is an OLTP type of application. Records are being added,updated, and deleted on a daily basis. At the end of the day, records are updated and moved from current work day tables, to history tables. I think that’s all the information, I don’t think I’ve missed anything. But I’m trying to understand why this is happening, and what is causing it. Any ideas as to why rows would go missing from the subscriber databases? Both are missing the same rows, as if it had never been added, or deleted before the replication got a chance to delete it. The stored procedure that keeps failing seems to be the delete command. Is it possible that there is some kind of conflict or locking that doesn’t allow the transactions to get sent to the distribution database or something? Just an idea. But all comments and questions are welcome. TIA
Karina
Can you post the error you are getting when replication failed? MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Make sure users are not capable of executing update/delete statement on the subscriber database. Jon M
Well, the error message is ‘row cannot be found’. And when I check the database at the subscribers, sure enough, the row isn’t there. As for users accessing the database…the users do have read/write access, but I’m pretty confident that the customer isn’t deleting these records. There isn’t a way in the system to delete single records like this, and they don’t have the knowledge to use T-SQL to make changes. I don’t think that the remote users have the abiltiy to do this. I’m thinking it may be related to this deferred updates operation that the server does. So I started the trace flag that enables the singleton updates, in hopes that it will minimize the occurance of the missing records. Right now we are monitoring the issue. The last two incidents were about a week apart. And we are creeping up to the one week mark from the last occurance. But I will keep this thread up to date with how this works out. Thanks so much for your input.
Karina.
Are there any recent changes happened to SQL server instance including both replicated instances? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
99% of the time "Row can’t be found" error means…some one or some app or procedure deleted the data…and some times you get the error "Can’t insert duplicate row/data" means some one iserted the row at the subscriber… You can resolve this issue in two ways…
1. Initialize the snapshot which means table at the subscriber not availabel during the time of snapshot…
2. Insert the row manually at the subscriber… you can find which one you need ot inserted from distribution agent history…. Note: Check the row count on publisher and subscriber to see how many rows missing…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
I see what you’re saying. It just seems very odd because the data missing seems somewhat insignificant, so I don’t see any reason for someone to delete it. I don’t think it was a stored procedure or the app, because the app calls the stored procs and there is no way through the app to delete a small data set like this. I could be wrong though; I’ll keep that in mind. No changes on the any servers. As for fixing it, it was less than ten records that were missing from both backup servers, so I just copied them back in, and everything went on from there with no problems. I really think it has something to do with the deferred updates…but that’s just me. I added the trace flag for singleton updates, and I#%92m monitoring the issue. I haven’t added it to the server start up parameters yet but I might do so. There haven#%92t been any recurrences yet, so I’ll have to wait and see. Thanks so much for your input everyone. K.
If any users has the permissions remove the write access to the replicated objects at the subscriber…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
]]>