FAST deletion with minimum log

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

FAST deletion with minimum log

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-03-08 : 08:42:25

I have a set of tables in which I have to do deletion in a transaction

My problem is that some tables have possibly million records so I have put chunking logic to delete

Please suggest I can do any improvement in the following logic

declare @tvpJobKeys as tvpJobKeys
declare @tvpProblamaticJobs as tvpJobKeys

SET NOCOUNT ON

insert into @tvpJobKeys
Select ID
From MyMainTable
where imjIsDeleted = 1
order by CAST(PQTRecordCount AS int) desc

declare @NumberRecords int
declare @RowCount as int

Set @RowCount = 0

select @NumberRecords = count(*)
from @tvpJobKeys

print 'Total Number of soft deleted Job ' + Cast(@NumberRecords as varchar(max))

declare @ID as varchar(8)
declare @chunksize as int

Set @chunksize = 5000

WHILE @RowCount <> @NumberRecords
begin
BEGIN TRY
BEGIN TRANSACTION

select @ID = ID
From (Select top 1 ID
From @tvpJobKeys) as tblJob

Update MyMainTable
Set imjIsThisAReplicaJob = 0,
imjReplicaJobCreateDuration = Null,
imjReplicaJobCreatedDate = NULL,
imjReplicaParentID = Null,
imjIsUpgraded = 0
Where imjReplicaParentID = @ID

print 'Job ' + @ID + 'Update MyMainTable '

WHILE ( Exists(Select top 1 *
From PieceDetailRecord
where fkID = @ID) )
BEGIN
DELETE TOP (@chunksize) -- reduce if log still growing
From PieceDetailRecord
where fkID = @ID
END

print 'Job ' + @ID + ' Delete PieceDetailRecord'

WHILE ( Exists(Select top 1 *
From PieceBarcodeRecord
where fkID = @ID) )
BEGIN
Delete top (@chunksize) From PieceBarcodeRecord
where fkID = @ID
End

print 'Job ' + @ID + ' Delete PieceBarcodeRecord'

WHILE ( Exists(Select top 1 *
From PackageQuantityRecord
where fkID = @ID) )
BEGIN
Delete top (@chunksize) From PackageQuantityRecord
where fkID = @ID
End

print 'Job ' + @ID + ' Delete PackageQuantityRecord'

Delete From PostageAdjustmentRecord
where fkID = @ID

print 'Job ' + @ID + ' Delete PostageAdjustmentRecord'

Delete From OriginalContainerIdentificationRecord
where fkID = @ID

print 'Job ' + @ID + ' Delete OriginalContainerIdentificationRecord'

Delete From MailPieceUnitRelationShipRecord
where fkID = @ID

print 'Job ' + @ID + ' Delete MailPieceUnitRelationShipRecord'

WHILE ( Exists(Select top 1 *
From IntelligentMailRangeRecord
where fkID = @ID) )
BEGIN
Delete top (@chunksize) From IntelligentMailRangeRecord
where fkID = @ID
End

print 'Job ' + @ID + ' Delete IntelligentMailRangeRecord'

WHILE ( Exists(Select top 1 *
From ContainerQuantityRecord
where fkID = @ID) )
BEGIN
Delete top (@chunksize) From ContainerQuantityRecord
where fkID = @ID
End

.....

WHILE ( Exists(Select top 1 *
From MyMainTableError
where fkID = @ID) )
BEGIN
Delete top (@chunksize) From MyMainTableError
where fkID = @ID
End

print 'Job ' + @ID + ' Delete MyMainTableError'

Delete From MyMainTable
where ID = @ID

print 'Job ' + @ID + ' Delete MyMainTable'

print 'Job ' + @ID + ' ' + Cast(@RowCount as varchar(max))

delete from @tvpJobKeys
where ID = @ID

print Cast(Getdate() as varchar(max)) + ' Job Deletion completed'

print '------------------------------------------------------------------------------------'

COMMIT TRANSACTION

SET @RowCount = @RowCount + 1
END TRY

BEGIN CATCH
delete from @tvpJobKeys
where ID = @ID

insert into @tvpProblamaticJobs
Select @ID
END CATCH
END

print Cast(Getdate() as varchar(max)) + ' All Deletion completed'

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 10:29:58

can you explain the logic behind using loop for deletion? why cant you apply set based logic instead? whats it that you're trying to iterate on?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-08 : 10:33:42

The only "problem" with your existing code is that the WHILE...DELETE TOP (@chunksize) loops are unnecessary; you're deleting all the affected rows in a single transaction anyway. This won't affect or improve the log growth.

I removed the loops and interstitial print statements and made a few minor tweaks, but this is essentially the same:

SET NOCOUNT ON

DECLARE @tvpJobKeys AS tvpJobKeys
DECLARE @tvpProblamaticJobs AS tvpJobKeys
DECLARE @NumberRecords INT, @RowCount INT = 0, @ID VARCHAR(8), @chunksize INT = 5000

INSERT INTO @tvpJobKeys
SELECT ID FROM MyMainTable WHERE imjIsDeleted = 1
ORDER BY CAST(PQTRecordCount AS INT) DESC

SELECT @NumberRecords = @@ROWCOUNT

PRINT 'Total Number of soft deleted Job ' + CAST(@NumberRecords AS VARCHAR(MAX))

WHILE @RowCount <= @NumberRecords
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION
			SELECT TOP 1 @ID = ID FROM @tvpJobKeys

			UPDATE MyMainTable
			SET imjIsThisAReplicaJob = 0,
			imjReplicaJobCreateDuration = NULL,
			imjReplicaJobCreatedDate = NULL,
			imjReplicaParentID = NULL,
			imjIsUpgraded = 0
			WHERE imjReplicaParentID = @ID

			DELETE FROM PieceDetailRecord WHERE fkID = @ID
			DELETE FROM PieceBarcodeRecord WHERE fkID = @ID
			DELETE FROM PackageQuantityRecord WHERE fkID = @ID
			DELETE FROM PostageAdjustmentRecord WHERE fkID = @ID
			DELETE FROM OriginalContainerIdentificationRecord WHERE fkID = @ID
			DELETE FROM MailPieceUnitRelationShipRecord WHERE fkID = @ID
			DELETE FROM IntelligentMailRangeRecord WHERE fkID = @ID
			DELETE FROM ContainerQuantityRecord WHERE fkID = @ID
			DELETE FROM MyMainTableError WHERE fkID = @ID
			DELETE FROM MyMainTable WHERE ID = @ID
			DELETE FROM @tvpJobKeys WHERE ID = @ID
			PRINT 'Job ' + @ID + ' ' + CAST(@RowCount AS VARCHAR(MAX))
			PRINT CONVERT(char(19), GETDATE(), 120) + ' Job Deletion completed'
			PRINT '------------------------------------------------------------------------------------'

		COMMIT TRANSACTION

		SET @RowCount = @RowCount + 1
	END TRY

	BEGIN CATCH
		DELETE FROM @tvpJobKeys WHERE ID = @ID
		INSERT INTO @tvpProblamaticJobs VALUES(@ID)
	END CATCH
	--CHECKPOINT
END

PRINT CONVERT(char(19), GETDATE(), 120) + ' All Deletion completed'

The fastest way to delete with minimal logging is to use SIMPLE recovery mode on your database. If you add a CHECKPOINT after each iteration of the outermost WHILE loop (I commented it out in my example), this will clear the log of active transactions and will minimize growth.

If your tables have no foreign keys, AND you're deleting a substantial amount of data (more than 25% of the total) then an even faster way is to export the data you want to keep, TRUNCATE all the affected tables, then import that data back using bcp or BULK INSERT. Since the TRUNCATE TABLE operation is minimally logged this will reduce overall log growth to the lowest possible size. However it will cause your database to be unavailable while the operation runs.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 10:38:19

the code has few fkIDs so i think foreignkeys do exist

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-03-09 : 00:27:15

Yes foreign keys do exists. Also suppose i doesn't have enough privileges to change the recovery model (which i found out as Full by default) then how can I best tweak above possible logic.

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-09 : 06:34:25

What do you mean by "tweak"? If your main goal is to minimize log growth, there's little else you can do other than add transaction log backups to your script, or add a delay to allow a regularly scheduled log backup to run before you start the next iteration.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 11:50:02

quote:
Originally posted by kamii47

Yes foreign keys do exists. Also suppose i doesn't have enough privileges to change the recovery model (which i found out as Full by default) then how can I best tweak above possible logic.

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

you still didnt explain why you're doing loop based delete?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-03-12 : 01:28:36

I wanted to either delete whole Relational entity or not.
That's why i first identify all the id's then loop through each of them in such a way that all data of an entity will delete or non

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 10:13:00

quote:
Originally posted by kamii47

I wanted to either delete whole Relational entity or not.
That's why i first identify all the id's then loop through each of them in such a way that all data of an entity will delete or non

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

then also it never calls for row by row deletion
you can use set based logic inside transaction to get all or none effect

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources