Delete Data from a Large Table in BatchPosted: September 24, 2014
Sometimes, you may have a need to clean up some tables to remove obsolete data. If the row size is too big, you may run into a long running process.
You can cut the operation in small batches to run. Here is an example:
declare @Chunk int=500, @ToBeDeleted int = 1 WHILE @ToBeDeleted > 0 BEGIN BEGIN TRAN DELETE TOP (@Chunk) FROM [dbo].[accountlog] WHERE createdate < Dateadd(day, -180, Getdate()) SET @ToBeDeleted = @@ROWCOUNT PRINT 'Deleted rows: ' + Cast(@ToBeDeleted AS VARCHAR(10)) COMMIT TRAN END