This might be useful. I had to use this a couple years ago, to clean up.
CREATE PROCEDURE dbo.mark_auto_archive_procedure_runtime
AS
BEGIN
DECLARE @loopcount INT,@rowcount INT, @increment INT
SET @loopcount = 0
SET @rowcount = 0
SET @increment = 100
print 'Going to mark the autoArchive flag in the C2ORuntimeObjectsArch table';
SELECT @rowcount = COUNT(rtArch.OID) FROM C2ORuntimeObjectsArch rtArch with (NOLOCK) WHERE rtArch.autoArchive = 0 AND Ended <= DATEADD(d, -25, getdate())
print 'Total number of rows eligible of marking ' + Convert(varchar(50), @rowcount);
WHILE @loopcount <= @rowcount
BEGIN
BEGIN TRY
BEGIN TRANSACTION
UPDATE TOP (CAST(@increment AS INT)) C2ORuntimeObjectsArch set autoArchive=1 WHERE ( autoArchive = 0 AND Ended <= DATEADD(d, -25, getdate()));
COMMIT TRANSACTION
SET @loopcount = @loopcount + @increment;
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
-- Optional delay between the batches can set here. Here it is a delay of 5 secs. The downside is that
-- no loggers would appear whole stored procedure is executed.
print 'Sleep for 5 secs';
WAITFOR DELAY '00:00:05';
END
END
exec dbo.mark_auto_archive_procedure_runtime
drop procedure dbo.mark_auto_archive_procedure_runtime