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:

SET @BatchCount = 100000

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

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


Leave a Reply

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

You are commenting using your 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: