How to delete the rows with out affecting log?

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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: