INF: Unsubscribe/Delete Publication Takes Too Long or DeadlocksID: Q190689
|
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.
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 Last Reviewed: April 20, 1999