SQL Replication issue

We have a SQL replication problem between a pair of MS SQL 2008 Servers. We're getting the following error and seeing some differences in data:

"Replication-Replication Distribution Subsystem: (server info) agent failed. The row was not found at the Subscriber when applying the replicated command."

We tried to reinitialize the the subscription and that doesn't appear to have helped. I'm running a compare right now with Red Gate's data compare, but could use some advice on finding and fixing the problem.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
This problem happened when update or delete command is replicating to subscriber and subscriber does not have that record to update or delete.

Redgate data compare can show you the difference. But please be carefull while sysncing from it because this will show you new inserted records at publisher in the difference list and if you inserted the records using redgate replication will go into error state again saying duplicate primary key... something similar.

first try to insert the individual record, might be this is the only record missing. if error resolve then good else.

You can scriptout the create subscription script and drop the subscription
then sync data manually using redgate or using backup.
create subscription again using scripts.
Deepak ChauhanSQL Server DBACommented:
Assuming you are using Transaction replication Push type, you will need to execute these stored procedures by filling correct parameters at publisher.

use <Publisher DB Name>
exec sp_addsubscription @publication = N'< >', @subscriber = N'< >',
@destination_db = N'< >', @subscription_type = N'Push', @sync_type = N'automatic',
@article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'< >', @subscriber = N'< >',
 @subscriber_db = N'< >', @job_login = < >, @job_password = < >,
 @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1,
 @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4,
 @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959,
 @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
willp2Author Commented:
Redgate finally ran out of space and I'll have to start over again after a few hours of comparing. I have a backup of the source database from last night. Are you saying I can restore that database to the target machine and we'll be OK?

No idea how to scriptout the create subscription script or recreate the subscription via scripts. Any info on this would be appreciated.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

willp2Author Commented:
Sorry, didn't see your second comment until after I submitted that last comment. What exactly does that do?
Deepak ChauhanSQL Server DBACommented:
Alright.  Can you tell me which type of replication is this? I am assuming this is Transactional replication Push type.

You can connect sql server using ssms and expand replication folder right click on the publication which went to error and generate script. once you generated the script you will see at the bottom of script file
the above two commands which i shared above.

exec sp_addsubscription + parameters
exec sp_addpushsubscription_agent + parameters

You have to set @job_login = < >, @job_password = < >, parameters everything else will be already your hand in the script.

Now expand the publication and delete the subscription (NOTE:: only Subscription not Publication)

Take backup of publisher database, overwrite it to subscriber database.

execute the scritps in publisher database.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Deepak ChauhanSQL Server DBACommented:
This will create a new subscription for the publication and all data will be in sync as of now.
willp2Author Commented:
Thanks very much for all the help.

It looks like at least part of the problem may that there is an issue with the Distribution database and I think I need to restore it.

Getting this error "CHECKDB found 0 allocation errors and 4 consistency errors in database 'distribution'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (distribution).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

The last good backup of this database was a few days ago. Can you or anyone provide some insight into restoring this database and what I need to do to get replication working again following a restore?

Deepak ChauhanSQL Server DBACommented:
Four concestency error, first check the msdb.dbo.suspect_pages table.

Stop log readers for all publications.
Take backup of distribution database.
Restore it on test server.
execute DBCC command

note down all corrupted page number
execute dbcc page () command

copy data stored on the pages.
run dbcc checkdb command with repair option.

check how much data lost. if it was old data then you can run repair command on production Distribution DB.
willp2Author Commented:
Thanks very much for you help with this. I ended up removing all replication and disabling replication which dropped the distribution database. Then recreated replication and everything is working well now.

Thanks again, your comments were very helpful and look like they should have worked well.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.