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.