PRB: Replication Cleanup Takes Too Much Resources, Cannot Run Multiple Times Due to Blocking (231328)
The information in this article applies to:
This article was previously published under Q231328 SYMPTOMS
In SQL Server 6.5, the default schedule for cleanup is once a day, at 1:05 A.M. This means that it removes 24 hours' worth of entries in MSjobs, MSjob_commands, MSsubscriber_jobs, and MSsubscriber_status at once. In high-volume replication installations, this results in the deletion of a huge amount of data in a single transaction, thereby consuming a great deal of resources, such as memory, locks, and log space, and it could possibly block other replication tasks.
WORKAROUND
To work around this problem, you can schedule the cleanup task to run more frequently, so that it has less information to remove at each execution. In cases in which this is a problem, you can use a wrapper procedure to call sp_replcleanup with a retention value in steps so that cleanup does its work in batches.
This procedure includes a stepsize parameter that controls how much the retention value will be reduced. This value is in number of hours; if you set it to 1, the procedure will execute sp_replcleanup 24 times per day. Please note that start_retention is assumed to be 24 hours: 0 hours of retention (default) + 24 hours since the last run. With a step size of 6 hours, this will run cleanup 5 times. Using this method, the log size, memory requirements, and so forth will be more manageable.
The following is the sample stored procedure:
use distribution
go
drop procedure sp_replcleanup_steps
go
CREATE PROCEDURE sp_replcleanup_steps /* run cleanup in steps */
@publisher varchar(30),
@subscriber varchar(30),
@retention int,
@stepsize int /* number of hours in each step */
as
begin
declare @start_retention int
select @start_retention=24 /* assumes your retention to be 0; change if
necessary */
while (@start_retention >= @retention) /* still need to stop at
@retention */
begin
exec sp_replcleanup @publisher,@subscriber, @start_retention
select @start_retention = @start_retention-@stepsize
end
end
In the cleanup task, you must call this procedure instead of sp_replcleanup. The command portion should look like:
distribution..sp_replcleanup_steps <publisher_name>, <subscriber_name>, <retention_value -- default of 0>, <step_size>
where < step_size> can be any value, preferably a number that 24 will divide by evenly.
MORE INFORMATIONNOTE: Be sure to test this procedure before putting it into production use. You may want to add more checks to handle cases in which step_size is greater than retention or other possible error conditions. This is intended to be a sample for calling sp_replcleanup in steps. Hence, if you run into any problems due to this procedure, you will have to revert back to the original setup of the cleanup task.
Modification Type: | Major | Last Reviewed: | 10/3/2003 |
---|
Keywords: | kbprb KB231328 |
---|
|