SQL 2012 Transactional Replication error: The log scan number passed to log scan in database is not valid.

  • Article

Hello,

Restoring a cdc enabled database with Transactional Replication from Prod to UAT. Once the database was restored with cdc and replication was configured I was getting the below errors.

Error: 14151, Severity: 18, State: 1. Replication-Replication Transaction-Log Reader Subsystem: agent ********  failed. The process could not execute ‘sp_MSadd_replcmds’ on ‘**********’.

The process could not execute ‘sp_repldone/sp_replcounters’ on **********.

The specified LSN {00000000:00000000:0000} for repldone log scan occurs before the current start of replication in the log {000649e8:0002418e:00b6}.

The process could not set the last distributed transaction.

Error: 9003, Severity: 20, State: 1.

The log scan number (********) passed to log scan in database ‘*****’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

Some of the blogs suggested -

1)DBCC CheckDB with allowdataloss

2)sp_repldone, sp_replflush

3)sp_replrestart

4)Reconfigure the publications.

When I checked the database the DBCC went clean, however I did the sp_repldone & sp_replflush and this was quick. I started sp_replrestart, but almost 12 hours and it was still running with no blockages; later I terminated this. I also tried to Reconfigure the publications with a different set of backup but it gave the same errors.

How did I Resolve:

1) Deleted all subscribers

2)Deleted all publications (note that other publishers were also deleted)

3)Disabled Distribution

4)Restart SQL service. (Note that after restarting the service my DB was into recovery for sometime as it had to rollback after the sp_replrestart)

5)Start from scratch – Enable Distributor, configure all Publishers & subscribers.

6) Reinitialize all subscribers.

7)No errors now.. Be happy http://alexaustin1.blog.com/wp-includes/images/smilies/icon_smile.gif