INF: Replication: Cleanup Task Takes a Long Time to Complete

ID: Q191412


The information in this article applies to:


SUMMARY

The cleanup task may take a very long time to complete (hours or even days) when there are a large number of rows in the distribution database. The logreader and distribution tasks cannot do any work while the cleanup task is running because the cleanup task acquires table locks on several tables in the distribution database. You can detect this situation using the sp_who and sp_lock stored procedures, as well as the Replication counters in the Windows NT Performance Monitor.

If the cleanup task is interrupted for some reason, it will go into rollback. The rollback process can also take a very long time to complete (hours or even days). This article discusses other options to clean up the distribution database under some conditions.


MORE INFORMATION

If the cleanup task has already started, it is usually best to wait for the process to complete, if possible. If you stop SQL Server or kill the cleanup process, it will roll back the work that has already been done. The rollback may take as long or longer to complete than the period of time the cleanup process has already been running.

However, additional options are also available in certain circumstances.

If You Want to Unsubscribe All Subscriptions from All Publications

If you want to unsubscribe all subscriptions from all publications, instead of running the cleanup task, it may be faster to manually truncate certain tables in the distribution database and delete related .tmp files. Also, at all the subscribers, you need to update MSlast_job_info to have a job_id of 0 for all publishers using this distribution server.

You should only consider truncating tables in the distribution database if all of the following conditions are true:
In this case, the tables in the distribution database that you would need to truncate are:

   MSjobs
   MSjob_commands
   MSsubscriber_jobs
   MSsubscriber_status 

You will also need to delete the .tmp files in the replication working directory (MSSQL\Repldata). The .tmp files are old bulk copy program (bcp) files used by synchronization. Note that new .tmp files will be created when you resubscribe to the publication.

As for updating MSlast_job_info table at the subscribing database, the job_id should be set to 0 for all publishers that use this server as the distribution server. Because the truncate operation removes all the entries in MSjobs table in the distribution database, the job_id gets reset to 0.

Drop and Re-Create the Distribution Database

In some situations, the best solution may be to drop and re-create the distribution database. This may be the case when all of the following circumstances are true:
For additional information about how to re-create the distribution database, please see the following article in the Microsoft Knowledge Base:
Q190797 : INF: Replication: How to Rebuild the Distribution Database

Additional query words: prodsql rep repl hang hangs hung abort aborted log
reader


Keywords          : SSrvRep 
Version           : WINNT:6.5
Platform          : winnt 
Issue type        : kbinfo 

Last Reviewed: April 16, 1999