Replication – Log reader stuck on Initializing – ‘The process could not execute ‘sp_repldone/sp_replcounters’ on ‘Server\Instance’.

Scenario – Database schema being upgraded on a replicated SQL to SQL database. The subscription is removed and both the publisher and subscriber schema’s are upgraded (DDL and DML changes). The subscription is added back in after completion of upgrade without initialisation as the consistency was kept while upgrading the schema. However, on startup of logreader, it starts scanning through a 10gb log on the publisher database to find pending replicated transactions.

Why?

Because the publication is still active, any articles in the publication which have DML against them will be marked in the transaction log as pending replication. This will cause 2 problems:

1. The log will not be able to be backed up until the pending transactions have been pushed  to the subscribers.

2. Constrainst will probably be violated and replication will break as the upgrade DML has already been run on the subscriber and these transactions should not be run again.

What to do?

As the database is in a stable state with both the subscriber and publisher at the same point of time, and because all user connectivity has been broken prior to the work, all the data in the transaction log needs to be ignored for the purpose of replication.

If you try to backup the transaction log with pending transactions still in the log, you will see the following error:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

To clear the pending transactions, run the following against the published database, after stopping the logreader and distribution agent:

exec sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1

If you try to start the log reader now you will see the following error:

2009-11-25 22:40:00.995 Status: 0, code: 18752, text: ‘Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log-related procedure.’.
2009-11-25 22:40:00.995 Status: 0, code: 22017, text: ‘The process could not set the last distributed transaction.’.
2009-11-25 22:40:00.995 Status: 0, code: 22037, text: ‘The process could not execute ‘sp_repldone/sp_replcounters’ on ‘Server\Instance’.

To clean this up,, run the following on the publisher database:

sp_replflush

This gives replication a clean slate and a backup of the log will now work as well as the logreader not trundling through 10gb worth of pending transaction.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: