February 26, 2013 at 5:31 am
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 🙁
February 27, 2013 at 8:42 am
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?
February 27, 2013 at 12:22 pm
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....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply