Microsoft KB Archive/183081

= BUG: Repl Cleanup Does Not Remove MSjobs/MSjob_commands Info =

Article ID: 183081

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q183081



BUG #: 17811 (SQLBUG_65)



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



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5.

Additional query words: repl partial removal comparison

Keywords: kbbug kbpending KB183081

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.