CODEWITHSUNDEEP
sql-serversql-server-2012
Ashish Shukla
Ashish Shukla

Reputation: 1294

Bulk data deletion query is getting timeout

I have table dbo.Logs in SQL Server in which I am storing application logs, More than 100k rows are getting generated daily on this table. So I have written a job to delete the data periodically from the table.

I am simply using SQL DELETE query to delete data from the Logs table. When there is too many rows in the table (approx. 2,000,000) then this DELETE statement query is getting timeout from SQL SERVER management studio.

Does anyone have idea to fix the issue, I tried increasing the query timeout but the same is not working.

Upvotes: 0

Views: 834

Answers (1)

LukStorms
LukStorms

Reputation: 29647

Delete them in chunks that don't cause a timeout.

For example:

SET NOCOUNT ON;
DECLARE @r INT = 1;
 
WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;
 
  DELETE TOP (100000) 
  FROM dbo.Logs
  WHERE CreatedAt < DATEADD(month,-3, DATEADD(day,1, EOMONTH(GETDATE())));
 
  SET @r = @@ROWCOUNT;
  COMMIT TRANSACTION;
END

Upvotes: 2

Related Questions