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

ID: Q191412

The information in this article applies to:

Microsoft SQL Server version 6.5

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:

You want to unsubscribe ALL subscriptions for all publications. This point is critical, because truncating the tables in the distribution database will remove all job information for all publications. If this criteria is not true, you should run the cleanup task.

It would take a long time to run the cleanup task. Usually, it is easier and safer to run the cleanup task. The time that would be required to run the cleanup task depends on the number of rows in the MSjobs, MSjob_commands, MSsubscriber_jobs, and MSsubsriber_status tables. There is no simple formula to calculate how long it will take to run the cleanup task. The best way to estimate the length of time required is to look at the task history to see how long it took to run the cleanup task under similar circumstances in the past.

There is no cleanup task already running or in rollback.

There are no replication tasks running. It may be best to just stop SQL Executive on the distribution server to ensure that no replication tasks are running.

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:

The cleanup task has stopped responding, or has been stopped and is in a long rollback.

You cannot wait for the task or rollback to complete.

Unsubscribing and resubscribing all subscribers for all publications is not a problem. Note that this is required because if the distribution database is dropped, you have to resubscribe.

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