INF: Unsubscribe/Delete Publication Takes Too Long or Deadlocks

• Microsoft SQL Server version 6.5

SUMMARY

Unsubscribing from a publication can take anywhere from a few seconds to a many hours. In cases where the unsubscribe process takes a long time, you may not want to wait for the unsubscribe process to complete, or may believe that the unsubscribe process has stopped responding. If you are using SQL Enterprise Manager to unsubscribe, you will see an hourglass icon until the unsubscribe process is complete.

Also, sometimes the process of unsubscribing may deadlock with other tasks and be chosen as the deadlock victim. If this is the case, the entire process must be repeated.

NOTE: If you delete a publication that has subscriptions, all of the subscriptions will be unsubscribed before the publication is deleted.

MORE INFORMATION

Unsubscribing from an article involves deleting records in the distribution database for commands that are not yet distributed to the specified subscriber. The length of time it takes to unsubscribe depends on the number of rows in the MSjobs, MSjob_commands, MSsubscriber_jobs, and MSsubscriber_status tables in the distribution database. The algorithm to delete rows involves joins of these tables, which become very time consuming as the size of the tables increases and the statistics information is not up to date.

The best way to minimize the amount of time required to unsubscribe is to run the cleanup process before unsubscribing any publications. The cleanup task looks into MSsubscriber_status for the max(job_id) that has been distributed to this subscriber. Then it removes all entries in MSjobs, MSjob_commands, and MSsubscriber_jobs up to that job_id, leaving the record with the max job_id. This process removes all distributed job information from the distribution database and leaves the tables with just the non- distributed jobs. Then you should run UPDATE STATISTICS to capture the latest statistics from the current dataset.

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

The unsubscribe process may run into deadlocks with logreaders. If this occurs, if possible, stop the logreaders and let the unsubscribe proceed. If there are many records in the distribution database pending distribution to the subscriber OR if the logreaders cannot be temporarily disabled, it may be worthwhile to update MSsubscriber_status to set the job_id of the record that contains the max(job_id) for this subscriber to be the max(job_id) from MSsubscriber_jobs for this subscriber. Then, if you run cleanup, all these records will be removed by the cleanup task. And because the cleanup task obtains table locks on all the tables involved, there is a reduced possibility of it running into a deadlock. After the cleanup task completes, the unsubscribe process should have nothing left to remove in the distribution database, and therefore should complete immediately.

Additional query words: hang hung hangs SEM hour glass unsub lockup

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