Replication data out of sync

Question

Replication data out of sync

Guras

SSCertifiable

Points: 6518
More actions
November 26, 2012 at 10:22 am

#393670

SQL server 2008 R2
Some replicated rows are missing in the subscriber table. How can I resync it?
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

You can accomplish this by re-running the snapshot agent for that subscription.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Guras SSCertifiable Points: 6518 More actions · Answer 2

I re ran the agent but still the rows are mising in the subscriber.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

What errors are you getting?

Also, what result do you get after running DBCC CHECKTABLE( 'YOURDB') WITH ALL_ERRORMSGS , NO_INFOMSGS on the subscriber?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Guras SSCertifiable Points: 6518 More actions · Answer 4

No error, everything is running fine just found out that some records are missing.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

Guras (11/26/2012)
I re ran the agent but still the rows are mising in the subscriber.

I going to assume we're not in Prod...

If the snapshot agent ran correctly your tables should be in sync ("in sync" as defined by your publication/subscription).

Check the the Replication Monitor for any errors. If you don't find anything actionable, try re-creating, then re-initializing the publication and subscription.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Guras SSCertifiable Points: 6518 More actions · Answer 6

Guras

SSCertifiable

Points: 6518

November 26, 2012 at 1:20 pm

#1562394

Unfortunately we are in the prod.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 7

What kind of replication are we talking about? I assumed it was transactional...and if you restarted the snapshot agent it would have automagically resnapped the entire table and everything would be 100% in sync.

Do you have any "Filters" defined in the article setup?

On the distributor, Check the publication "Replication Distributor" SQL Agent job to make sure no one has set the SkipErrors flag...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Guras SSCertifiable Points: 6518 More actions · Answer 8

It is a transactional replication. It is strange that I cannot find that job in my SQL Agent job list.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 9

Guras (11/26/2012)
Unfortunately we are in the prod.

First, I concur with MyDoggieJessie - check for filters, check the agent job to see if it is running without error...

I don't know what your policies are about Production - but, the sure-fire way to fix this will be to re-create the publication and subscription. If you have a window of time to do so, that would be the way to go. I have struggled with various Replication issues over the years (Merge, Transactional, etc) and, provided that:

1) my Distributor is setup and functioning correctly

2) my subscriber has rights to the publication and published data

... If these things are true then re-creating the publication and subscripton, then reinitializing it will solve your problem.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 10

The agent job will be wherever your distributor resides

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Alan Burstein SSC Guru Points: 61141 More actions · Answer 11

Guras (11/26/2012)
It is a transactional replication. It is strange that I cannot find that job in my SQL Agent job list.

The agent job will be running whereever the job is being pushed or pulled from. For example: Server A is Publisher, Server B is the subscriber. If you are running a Push subscription then the agent job will be on Server A, Pull subscription then the agent job will be on Server B.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Guras SSCertifiable Points: 6518 More actions · Answer 12

This is how I restarted the agent job. I went to the local publication , found the one for the table that had problem. Right click on it , Click view snapshot agent satus . Click on the start button.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 13

I've got a suggestion but first need to know how large is your publication? For instance, how many articles are in this publication? A few dozen? Hundreds? How long does it take for the snapshot agent to generate the snapshot? Since this is a production server, what is your exposure going to be and for how long can you work on the issue?

If the above answers are minimal, i.e a few dozen tables, amounting to a few hundred MB of data then you can try this:

Find out whether or not your publication allows the dropping/adding of single articles (this code must be run on the server acting as the distributor):

SELECT publisher_id, publication_id,

publisher_db,

publication, allow_anonymous, immediate_sync

FROM distribution.dbo.MSpublications WHERE publication_type = 0 If the results of this query show zero's for the "allow_anonymous" and "immediate_sync" columns, you're good-to-go, if not, you need to run this code on the publisher:

EXEC sp_changepublication @publication = 'YourPublisher', @property = 'allow_anonymous', @value = 'false'

GO

EXEC sp_changepublication @publication = 'YourPublisher', @property = 'immediate_sync', @value = 'false'

GO

Next go to your publication (I prefer to do all of the stuff below using the Replication Monitor GUI), right-click, go to "articles", and uncheck the article that's giving you all the trouble. This will give you one nasty pop-up dialogue that will probably freak you out stating something along the lines "this will invalidate your existing snapshot...yada, yada, yada" Ignore this...if you set up replication with the defaults, your initial snapshots were only good for 3 days anyway and its kinda a mute point. Click ok and I believe ok again to accept your changes.

Now, for the same publication, go back to the same place you unchecked the article, uncheck "Show only checked articles in the list", find the article you just dropped, and put a check in it. Click the ok's until your out of it all.

Finally, go to your snapshot agent for this publication and start it again. Pay attention to the status...should tell you a lot of useful info...

Once it completes, recheck your table, it should be as good as new - if not, please re-verify your "Filter", or put a trace on your table to find out how's removing the records from your subscriber's table!

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience