FIX: LogReader Can Fail On sp_repldone Using Partitioning

• Microsoft SQL Server, version 6.0

SYMPTOMS

Under certain conditions using horizontal or vertical partitioning, 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>.

CAUSE

This problem can occur if a transaction is run against a published article that does not meet the criteria of a horizontal or vertical partition. 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. Then 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 under these conditions if a transaction is executed that will fail the criteria of a horizontal or vertical partition (that is, implemented by a replication "filter procedure"). For more information on horizontal or vertical partitions, consult the SQL Server 6.0 "Administrator's Companion."

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.