PRB: Replication Cleanup Takes Too Much Resources, Cannot Run Multiple Times Due to Blocking

• Microsoft SQL Server version 6.5

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 

distribution..sp_replcleanup_steps <publisher_name>, <subscriber_name>, <retention_value -- default of 0>, <step_size> 

MORE INFORMATION

NOTE: 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.

Additional query words: repl tran xact proc sproc st sp

Keywords : kbSQLServ650 kbDSupport
Version : winnt:6.5
Platform : winnt
Issue type : kbprb