BUG: Repl Cleanup Does Not Remove MSjobs/MSjob

SYMPTOMS

The cleanup task does not remove the entries in the MSjobs and MSjob_commands tables when all the publications are "scheduled table refresh" type publications. This may eventually fill up the distribution database. The cleanup task does remove the entries from the MSsubscriber_jobs table, as well as the bulk copy program (bcp) data files (.tmp files) in the replication working directory.

CAUSE

The cleanup task calls the sp_replcleanup stored procedure, which removes the information in MSsubscriber_jobs and MSsubscriber_status based on the retention value. It removes records in the MSjobs and MSjob_commands tables based on non-zero transaction ID. For scheduled table refresh type publications, the transaction ID is always zero; therefore, the SELECT statement to get the max(job_id) with xactid_page/row <> 0 returns NULL.

The comparison with NULL returns False, and the delete does not remove any entries from MSjobs. Consequently, MSjob_commands records are not removed.

WORKAROUND

To work around this problem, drop and re-create sp_replcleanup with the following change in the section that deals with MSjobs entries:

   ---- begin ----
      /* Remove all orphaned jobs (no entry in MSsubscriber_jobs) from
         MSjobs */ 
      delete MSjobs from MSjobs j where
         j.publisher_id = @publisher_id and
         j.publisher_db = @publisher_db and
         j.job_id not in (select job_id from MSsubscriber_jobs sj (index =
         ncMSsubscriber_jobs) where
            sj.publisher_id = j.publisher_id and
      sj.publisher_db = j.publisher_db and
      sj.job_id = j.job_id) and
         j.job_id <> isnull ( /* added the isnull function */ 
      (select max(job_id) from MSjobs j (index = ucMSjobs) where
            j.publisher_id = @publisher_id and
            j.publisher_db = @publisher_db and
         j.xactid_page <> 0)
      ,0) /* if NULL, return zero instead of NULL */ 
      and
         j.job_id <> (select max(job_id) from MSjobs j (index = ucMSjobs)
      where
            j.publisher_id = @publisher_id and
            j.publisher_db = @publisher_db)
      if @@error <> 0
         begin
            close hC2
            DEALLOCATE hC2
            rollback transaction sp_replcleanup
            return (1)
         end
   ---- end ----

BUG: Repl Cleanup Does Not Remove MSjobs/MSjob_commands Info (183081)

SYMPTOMS

CAUSE

WORKAROUND

STATUS