I have a SQL/Trace, and it’s a requirement to each night delete all rows in the Trace over x days old. This is typically called a purge program, or mass delete. If you don’t limit the data, then you can knock the lights out of SQL.

Below is a modification to code I found here: http://dbadiaries.com/how-to-delete-millions-of-rows-using-t-sql-with-reduced-impact. That blog does a good job of explaining why we need to break the deletes into smaller chunks.

I added the “Wait” statement, which is optional. If you have other jobs running that might be impacted by your delete, you don’t want to block them. So you simply pause, give them a chance to get in and out, then continue with the deletes.

<pre>-- Technique to not cause SQL slowness by deleting large number of rows (such as millions of rows) 
-- at one time (without freeing locks) 
-- from http://dbadiaries.com/how-to-delete-millions-of-rows-using-t-sql-with-reduced-impact

declare @DaysToKeep int = 5
declare @RowsDeleted int = 1  -- must set &gt; 0 so the loop will be entered the first time through 
declare @TotalRowsDeleted int = 0 
declare @StartDateTime datetime = DATEADD(d, -@DaysToKeep, getdate())
declare @DateTimeTemp DateTime2
WHILE @RowsDeleted > 0
  BEGIN
         DELETE TOP (1000) FROM Trace where  trcDateTime <= @StartDateTime 
         set @RowsDeleted = @@ROWCOUNT 
         --print @RowsDeleted
   set @DateTimeTemp = CAST(GETDATE() as Datetime2 (3) )
   print CAST(@DateTimeTemp as varchar(max)) + ' RowsDeleted=' + CAST(@RowsDeleted as varchar(12)) 
         set @TotalRowsDeleted = @TotalRowsDeleted + @RowsDeleted 
         WAITFOR DELAY '00:00:01';  -- Give SQL a little break 
  END 
SELECT @StartDateTime as StartDateTimeOfDelete, 
       @TotalRowsDeleted as RowsDeleted, (select COUNT(*) from Trace) as RemainingRows, 
       MIN(trcDateTime) as Min, MAX(trcDateTime) as Max from Trace  

--select count(*) from trace where trcDateTime >= DATEADD(d, -15, getdate())
--declare @DaysToKeep int = 20
--select DATEADD(d, -@DaysToKeep, getdate())

</pre>