December 22, 2011 at 10:46 am
We've had a weird issue crop up twice now in the last month: (SS 2005)
1. We will have a replication problem where there are more rows in the subscribers than the publisher. It is very unlikely that someone is inserting rows in the subscribers, because these subscriber servers are completely different with different functions and users and so on.
2. I correct the problem by reinitializing and all seems to be well.
3. Then later in the day or evening I get a report that data off of the publisher from the previous day is gone, supposedly vaporized. Furthermore that data was definitely there previously as they ran reports off of it. Even more confusing, is that it is very unlikely that someone or some process is actually deleting this data.
So here is what I am wondering: can an undistributed or pending replication transaction somehow cause all these issues? Nothing seems to make sense, but it is causing major prod issues for us.
Any ideas would be much appreciated...
December 22, 2011 at 12:05 pm
Which flavor of replication are you using?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 22, 2011 at 12:06 pm
(Sorry for two posts. Had to take a minute to find this.)
Take a look at this, see if it helps: http://www.sqlservercentral.com/Forums/Topic1144535-391-1.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 22, 2011 at 12:18 pm
Sorry - push transactional immediate. We have a distributor server.
Intg article. I actually read something similar yesterday. The only thing is this: the data is not missing off of the subscriber but rather the publisher.
What it looks like is that a delete happens on the publisher but gets lost to the subscriber. In fact, the last two time this has occurred, we have pulled the missing data off of the subscribers either directly or through a restored backup.
However, the developers swear up and down that there is no process that deletes data out of this table and that they only do updates. So they whole thing is weird. We just put a trigger on the publisher, so hopefully that will help.
Also, I have a quick practical question: when I run sp_browsereplcmds or whatever it is, it takes foreever! What does that mean? I'm not sure I understand exactly what that thing is doing.
December 22, 2011 at 1:21 pm
A trace might be easier on your server than a trigger, and will capture the same kind of information.
A non-replicated transaction is a non-replicated transaction, whether it's an insert (missing row at subscriber) or a delete (missing row at publisher, or ghost row at subscriber, whichever way you want to look at it).
I'd definitely use a trace on the database (server-side trace, not Profiler GUI trace), looking for deletes. I've had a lot of devs swear a lot of things to me (and at me for that matter) about their database code, only to find they were mistaken about parts of it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2011 at 12:24 am
If database size is small than I prefer to create the replication setup again during off pick hours. Which will ensure you that all things are set up correctly and than see if are you facing the same issues.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply