Break large delete operations into chunks

On 11 Apr, 2015

T-SQL

By : Charith Silva

No Comments

When you performing a huge DELETE operation with or without where clauses, transaction log might take over SQL server hard disk and it might takes long time to complete. But delete in chunks: 50,000 or 100,000 rows at a time might solve this issue.

SET NOCOUNT ON;
DECLARE @rows INT;
DECLARE @chunksize INT;

SET @rows = 1;
SET @chunksize =100000;

WHILE @rows &gt; 0
BEGIN
  BEGIN TRANSACTION
  DELETE TOP (@chunksize)
  FROM [databasename]

  SET @rows = @@ROWCOUNT;
 COMMIT TRANSACTION
END

SET NOCOUNT ON;

DECLARE @rows INT;

DECLARE @chunksize INT;

SET @rows = 1;

SET @chunksize =100000;

WHILE @rows > 0

BEGIN

BEGIN TRANSACTION

DELETE TOP (@chunksize)

FROM [databasename]

SET @rows = @@ROWCOUNT;

COMMIT TRANSACTION

END

Previous Post Next Post

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva