How to delete the rows with out affecting log?

September 21, 2012

To delete the rows in large tables (audit/log related in general) we use the simple delete statement with criteria. But this approach would be affecting the transaction log growth. If we delete 10 million rows from a table then the transaction log will grow nearly 10 GB or more.

To avoid this we can use the delete command with batch processing as mentioned below:

DECLARE @BatchCount INT
SET @BatchCount = 100000

WHILE @BatchCount <> 0
BEGIN
    DELETE TOP (@BatchCount)
    FROM [dbo].[TableName]
    SET @BatchCount = @@rowcount
END  

The above query batch will reduce the log file head ache and will delete the rows batch by batch.

 


%d bloggers like this: