Microsoft KB Archive/231328

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

Article ID: 231328

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



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 , , ,  where  can be any value, preferably a number that 24 will divide by evenly.



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: kbprb KB231328

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.