Delete Data from a Large Table in Batch


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 

 

 
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