Yes, this is a method I'm using to gradually purge old, un-needed data on various tables ( in this case deleting one at a time )
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET NOCOUNT ON;
---SET DEADLOCK_PRIORITY 10;
USE COLLxxxxxxxx;
GO
DECLARE @errmessage VARCHAR(150);
DECLARE @msg VARCHAR(300);
DECLARE @logpercentage BIGINT;
DECLARE @ErrMsg NVARCHAR(4000) ,
@ErrSeverity INT;
DECLARE @rowcount BIGINT;
DECLARE @counter INT;
SET @errmessage = 'Starting at ' + CAST(GETDATE() AS VARCHAR(20));
RAISERROR(@errmessage ,10,1) WITH NOWAIT;
---- Begin PurgeProdFile_Record.sql 1 hr 10 min perfqa
IF OBJECT_ID('tempdb..#tempdataFR') IS NOT NULL
DROP TABLE #tempdataFR;
CREATE TABLE #tempdataFR
(
ID INT IDENTITY(1, 1)
PRIMARY KEY ,
File_Record_ID INT
);
INSERT INTO #tempdataFR
SELECT TOP ( 2000000 )
FILE_RECORD_ID
FROM COLLxxxxxxxx.dbo.FILE_RECORD
WHERE FILE_RECORD.FILE_RECORD_ID IN (
SELECT FILE_RECORD.FILE_RECORD_ID
FROM FILE_RECORD
INNER JOIN MESSAGE ON FILE_RECORD.MESSAGE_ID = MESSAGE.MESSAGE_ID
INNER JOIN [FILE] ON [FILE].FILE_ID = FILE_RECORD.FILE_ID
WHERE FILE_RECORD.CONCRETE_TYPE = 'Fdi.Po.ClientFileRecord'
AND [FILE].CONCRETE_TYPE = 'Fdi.Po.ClientFile'
AND MESSAGE.CONCRETE_TYPE = 'Fdi.Po.ClientUploadMessage'
AND FILE_RECORD.MESSAGE_DIRECTION = 'OUTGOING'
AND FILE_TRANSFORMATION_COMPLETION_STATE = 'EXPORTED'
AND FILE_RECORD.CREATED_DATE_TIME < GETDATE() - 365 )
SET @rowcount = @@rowcount;
SET @errmessage = 'temp table for File_Record has '
+ CAST(@rowcount AS VARCHAR(20)) + ' at ' + CAST(GETDATE() AS VARCHAR(20));
RAISERROR(@errmessage ,10,1) WITH NOWAIT;
CREATE INDEX IDX_tempdataFRIdcolumn ON #tempdataFR(File_Record_ID);
SELECT @counter = MAX(ID)
FROM #tempdataFR;
SET @rowcount = 1;
WHILE @rowcount > 0
AND @counter > 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
DELETE TOP ( 1 )
FROM COLLxxxxxxxx.dbo.FILE_RECORD
WHERE FILE_RECORD.FILE_RECORD_ID IN ( SELECT File_Record_ID
FROM #tempdataFR
WHERE [ID] = @counter )
SET @rowcount = @@rowcount;
SET @counter = @counter - 1;
IF ( @counter % 10000 ) = 0
BEGIN
SET @errmessage = 'Counter down to '
+ CAST(@counter AS VARCHAR(20))
+ ' in File Record at '
+ CAST(GETDATE() AS VARCHAR(20));
RAISERROR(@errmessage ,10,1) WITH NOWAIT;
END;
COMMIT;
SELECT @logpercentage = cntr_value
FROM sys.dm_os_performance_counters
WHERE instance_name = 'COLLxxxxxxxx'
AND counter_name = 'Percent Log Used';
IF @logpercentage > 30
BEGIN
SET @msg = ' log more than 30 percent full, waiting 5 min';
RAISERROR (@msg,10,1) WITH NOWAIT;
WAITFOR DELAY '00:05:00';
END;
END TRY
BEGIN CATCH
-- There was an error
IF @@TRANCOUNT > 0
ROLLBACK;
-- Raise an error with the details of the exception
SELECT @ErrMsg = ERROR_MESSAGE() ,
@ErrSeverity = ERROR_SEVERITY();
RAISERROR (
@ErrMsg
,@ErrSeverity
,1
);
--DECLARE @emailmessage VARCHAR(350)
-- SET @emailmessage='Purge script failed: ' + @errmsg
--EXECUTE msdb.dbo.sp_notify_operator @name=N'TestOperator',@body=@emailmessage
END CATCH;
--------------WAITFOR DELAY '00:00:05';
END;
IF OBJECT_ID('tempdb..#tempdataFR') IS NOT NULL
DROP TABLE #tempdataFR;
--- End PurgeProdFile_Record.sql