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.