Iterative SQL Delete Statement The following is a simple version of a delete I need to perform.

delete from table_name
where order_date &lt; (getdate()-60)
go


This is deleting a large number of blobs and to clean up the table I need to reduce the number of days in small chunks to avoid TEMPDB filling up.

Is there an easy way of getting this to run in passes each time reducing the number of days by 1 starting at say 60 days and ending at 30 days?

Any help or pointers in the right direction would be great.
Depending on the amount of data being deleted, and if you are in the full or bulk-logged recovery model. Then you might want to look into adjusting the TOP N value and/or increasing your t-log backup frequency.

DECLARE @MinDate    DATETIME = DATEADD(DAY,-60, GETDATE())
        ,@Maxdate   DATETIME = DATEADD(DAY,-30, GETDATE())
        ,@RowCount  INT = 1

WHILE   @RowCount &gt; 0

BEGIN 

        DELETE  TOP (20000)
                tn
        FROM    table_name tn
        WHERE   order_date &gt;= @MinDate
        AND     order_date &lt;= @Maxdate

        SET @RowCount = @@ROWCOUNT   

    RAISERROR('%d rows deleted', 10, 0, @RowCount) WITH NOWAIT

END