FIX: LogReader Can Fail On sp_repldone After Unsubscribing

• Microsoft SQL Server, version 6.0

SYMPTOMS

If the last subscription to an article is removed while there are still outstanding unreplicated transactions in the published database log, the LogReader task for replication can fail with the following error:

   The replicated transaction (xxx, yyy) no longer exists in the log.
   Unable to execute sp_repldone on '<server>'.

The LogReader task will fail and be shutdown in this situation. No replication transactions for the published database will be processed until the error is corrected. Since the log cannot be truncated past the oldest unreplicated transaction, the log may fill to a point where Msg 1105 will be encountered.

WORKAROUND

Find the row in MSjob in the distribution database whose xactid_page and xactid_row match the values listed in the error message above. This row should contain the maximum job_id value for the table.

After you do this, update the values for xactid_page and xactid_row for this row to 0. It is recommended that you run the UPDATE statement with a BEGIN TRAN command so that you can rollback changes if you make a mistake. Make sure to execute COMMIT TRAN if the statement is successfully applied.

If the LogReader is setup as "Autostart," restart SQLExecutive. Otherwise, you may choose to execute the task "On Demand" to make sure it can run successfully. If the problem is resolved, the LogReader task will show up as a "Running Task" under SQL Enterprise Manager. However, it will not show a successful status in the Task History if you restarted SQLExecutive.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server 6.0. This problem was corrected in Service Pack 2 for SQL Server version 6.0. For more information, contact your primary support provider.

MORE INFORMATION

This problem has been observed only when the transaction reported in the error above has been truncated from the log and the LogReader task is restarted. If the LogReader task is never restarted, then the error may not occur. The problem can only occur if the all subscriptions have been removed for all articles in the publication.

Remember that the LogReader can be "restarted" by restarting SQLExecutive if the task is set to "Autostart." It can also be "restarted" by scheduling the task to run "Recurring" or executing it "On Demand." The problem can be encountered under any of these scheduled frequencies.