Writing A Transact SQL (TSQL) Procedure For SQL Server 2008 To Delete Rows From Table Safely

Posted by Peter Kellner on April 29, 2010 · 5 mins read

In this post, we will show and explain a small TSQL Sql Server 2008 procedure that deletes all rows in a table that are older than some specified date.  That is, say the table has 10,000,000 rows in it the accumulated over the past 2 years. 

Say you want to delete all but the last 30 days of activity.  If you just simply say DELETE FROM table WHERE id>10000, you will cause this to happen in one transaction and likely, you will get an error.  That’s the best case.  The worst case is your system tries to do this, eventually consumes all the resources in your computer and crashes your server.

 

CREATE PROCEDURE dbo.DeleteRowsInGroupsBeforeNdaysLog4NetAll
@NoOfRowsToDelete int,
@DaysBeforeTodayToDelete int
AS
BEGIN

DECLARE @DateToDeleteBefore date -- Number Of Days To Delete Records From
DECLARE @DateToday datetime -- To Hold Todays Date
DECLARE @PrintMessage varchar(256) -- To Hold Print Message
DECLARE @CurrentRowCount int -- Scratch Var For Counting Rows

SET @DateToday = GetDate()

SELECT @CurrentRowCount =
(
SELECT COUNT(*)
FROM dbo.Log4NetAll
WHERE Date <(@DateToday - @DaysBeforeTodayToDelete)
)

WHILE (@CurrentRowCount > 0) BEGIN
DELETE TOP (@NoOfRowsToDelete)
FROM dbo.Log4NetAll
WHERE Id IN (
SELECT TOP (@NoOfRowsToDelete) Id
FROM Log4NetAll
WHERE Date < (@DateToday - @DaysBeforeTodayToDelete)
ORDER BY Id ASC)

-- Count the records again
SELECT @CurrentRowCount =
(
SELECT COUNT(*)
FROM dbo.Log4NetAll
WHERE Date <(@DateToday - @DaysBeforeTodayToDelete)
)
SET @PrintMessage = N'Deleting This Many Rows: '
+ (CAST(@CurrentRowCount AS nvarchar(10)));
PRINT @PrintMessage;
END
END

* Inspired by a blog post by Suprotim Agarwal
 
Basically, what is happening in the above code is first, we are testing to see if there are any records that meet our criteria to delete.  That is, records older than a certain date.  Next, we have a WHILE loop that continue until there are no more records that meet the criteria.  The DELETE SQL command uses TOP, which is very handy because it will delete up to that many.  That way, you don’t have to worry about deleting the last few records with any special case code.
 
The only thing a little tricky that I did was to actually delete the records with Subquery that forces a sort of the records before deleting.  That is, by asking for Id IN (Records sorted ascending), I’m forcing my TOP command to delete the oldest records first.  This may not be necessary if your method runs to completion, but if you interrupt it, it might be convenient for the oldest records to be deleted first.

Then, do Execute the Procedure, you would call it as follows assuming you wanted to delete the code in batches.

 

DECLARE @NoOfRowsToDelete int;
DECLARE @DaysBeforeTodayToDelete int;

SET @NoOfRowsToDelete = 100;
SET @DaysBeforeTodayToDelete = 30;

EXEC [dbo].[DeleteRowsInGroupsBeforeNdaysLog4NetAll]
@NoOfRowsToDelete, @DaysBeforeTodayToDelete ;

The output of calling this might be:

Query execution was canceled by user request.

Deleting This Many Rows: 19199

Deleting This Many Rows: 19099

Deleting This Many Rows: 18999

Deleting This Many Rows: 18899

Deleting This Many Rows: 18799

Deleting This Many Rows: 18699

Deleting This Many Rows: 18599… (All the way until 0)

 

Hope this helps!