Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

data loss for all published tables by adding article to replication? Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 5:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:49 AM
Points: 187, Visits: 699
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
Post #1424024
Posted Wednesday, February 27, 2013 8:42 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:02 AM
Points: 28, Visits: 153
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?
Post #1424572
Posted Wednesday, February 27, 2013 12:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:49 AM
Points: 187, Visits: 699
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....
Post #1424683
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse