SQL SERVER – Transaction replication out of sync and subscription expiration

Usually Monday morning doesn’t work for DBA most of the issues are come on that day. One more issue related to Transaction replication in the morning task, we use to check the replication jobs status and other activity which was scheduled in night time.

Also use to check the replication status for the confirmation. We checked replication monitor and found that transaction replication was out of sync for the Bangalore location. There was bombarding of mails in my mailbox. After checking logs, we found that problem was related to the subscription expiration.

Now challenge was to resolve the issue without affecting the subscriber database table.

Critical Tables were involved in this replication and some extra columns were added to the table in Subscriber database. So we are not allowed to do the re-initialization of tables in subscriber end.
We queried on MSsubscriptions table in distribution database and found that status column in this table was set to 1 whereas for other databases it was 2, so we decided to update this column to avoid the re-initialization.

Before update MSsubscriptions table we took the backup of the existing the tables on subscriber end so that if anything goes wrong with update it will help us to bring back subscriber to the normal stage.

Rollback plan was ready with us while updating the table of distribution database.

select * from MSsubscriptions where subscriber_db = ‘databasename’

update MSsubscriptions
set status = 2 
where subscriber_db = ‘databasename’ 
and publisher_db = ‘databasename’

Surprise, after updating the table it start performing the synchronization all back logs were started to flow between publisher to distributor and distributor to subscriber.

Finally it worked for us and problem got resolved without reinitialize the tables of subscriber database.


Leave a Reply




− 3 = 3