SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


data loss for all published tables by adding article to replication?


data loss for all published tables by adding article to replication?

Author
Message
WolfgangE
WolfgangE
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1275 Visits: 798
Hi there,

I'm not sure if this is the right forum but I try:
yesterday I added an article to our transactional replication using sp_addarticle and sp_refreshsubscriptions. Worked fine.

BUT: It seems that we lost transactions. There are definitely some rows missing on our subscribers. All missing rows were inserted on the publisher about 12:50 o'clock, that's the time I added the article.
As we have many transactions it were about 5.000 records found in several different tables.

Does anyone share this experience?
Does anyone know a possibility to get the missing commands? (but I fear there didn't even get to the distributor) We do have an Update-Timestamp for all our records but the records could have been updated several times in the last view hours...

I already did a good job inserting the missing records to the subscribers but I can't determine the update commands that were not distributed Sad
craig.dixon
craig.dixon
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 177
Hi WolfGangE,

I think you may be having a similar issue to one I had a while back. Did you try generating scripts for both and comparing them? Or have you already solved the problem?
WolfgangE
WolfgangE
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1275 Visits: 798
hi Grasshopper,

there is no standard script for such cases. Yes, actually I compared any single table on each subscriber (thank goodness there are only 2).

In sys.objects I can see when the table was created and modified. The modify date seemed to be the date when the subscriptions where refreshed.
As we have an InsertDate in nearly all our tables I queried any replicated table on the publisher like the following:
select * 
into dbo.ReplDiff_TableXY
from dbo.TableXY
where InsertDate between ... and ...



I chose a time frame of 2 minutes.

As usually there is no index on the InsertDate it is essential to use READ UNCOMMITTED and of course the queries used some time to execute.

Then, on the subscriber, I queried like this:
insert into dbo.TableXY
where SomePKColumn not in ( select SomePKColumn from [LinkedServerToPublisher].myDB.dbo.ReplDiff_TableXY )



As the ReplDiff-Tables on the publisher did not contain many data the use of the linked server was no problem, otherwise I would have copied these ReplDiff-tables in a separate step to the subscribers.

But, as mentioned, you get the Inserts that way, but not the updates.

For generating the queries I used the system tables to get the table and primary key column names, like this:
select 'insert into ' + schema_name(schema_id) + '.' + object_name(object_id)
from sys.tables.....




I wonder I still did not find anything detailled about this problem. But I've never added a table during production time in such a huge system which gets several transactions per second....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search