Controlled DELETE

<< Something that I am dealing with today | Home | Disable all jobs on a SQL Server >>

Controlled DELETE

So how do you DELETE thousands/millions of rows from a table without blocking anyone for long periods of time?

Well, here is an example:

DECLARE @Count INT
DECLARE @Error INT

SELECT @Count = COUNT(*)
FROM SomeTable MLE
WHERE SomeDateColumn < (GETDATE() - 182) -- anything older than 6 months

SET ROWCOUNT 10000

WHILE @Count > 0
BEGIN

BEGIN TRAN

DELETE FROM SomeTable
WHERE SomeDateColumn < (GETDATE() - 182)

SELECT @Error = @@ERROR

IF @@ERROR = 0

COMMIT TRAN

ELSE

ROLLBACK TRAN

CHECKPOINT

SELECT @Count = COUNT(*)
FROM SomeTable
WHERE SomeDateColumn < (GETDATE() - 182)

END

SET ROWCOUNT 0
SET NOCOUNT OFF

Feedback

# re: Controlled DELETE

I usually try to avoid the actual count of table and just use the @@RowCount function.

It this case it would be set at the same time as the Error code.

Select @Error = @@ERROR, @Count = @@ROWCOUNT

10/15/2003 4:06 PM | DavidM

# re: Controlled DELETE

Thanks Graz. We've got several processes that do this, so I'll probably make this modification. The processes are pretty quick as it is, but it wouldn't work to make them better.

10/15/2003 4:09 PM | Tara

# re: Controlled DELETE

Very neat!!

10/16/2003 4:25 AM | Stoad

# re: Controlled DELETE

Graz?

10/16/2003 5:13 AM | robvolk

# re: Controlled DELETE

Damn, my last comment should say: but it wouldn't hurt to make them better.

10/16/2003 9:29 AM | Tara

# re: Controlled DELETE

I am glad, I am not alone! I implemented very similar logic, but found 1000 records per loop was the best balance I could strike. I tried this on a variety of tables with various row widths.

10/17/2003 2:10 PM | Satish Balusa

# re: Controlled DELETE

Yeah I'm not sure that the CHECKPOINT is needed either, but it would depend on how big your transaction log can get. We've got plenty of disk space for it to grow, so we could remove it.

10/20/2003 9:15 AM | Tara

# re: Controlled DELETE

Also I doubt about BEGIN TRANS.I remember nr told me when you USE single delete or update you don't need explicit transaction.
try out this:
declare @rowcount int
select @rowcount=1
SET ROWCOUNT 10000
while @rowcount>0
begin
DELETE FROM SomeTable
WHERE SomeDateColumn < (GETDATE() - 182)

select @rowcount=@@rowcount

end

10/20/2003 11:36 AM | joseph

# re: Controlled DELETE

You are right joseph. A single SQL statement is executed in an isolated atmoic state and it is processed as a transaction. So even I don't find any relevance of the explicit transaction declaration, unless I'm missing something.

A good tip by Tara.

10/21/2003 9:25 AM | Ashutosh Nilkanth

# re: Controlled DELETE

The code that I posted is just an example. It doesn't hurt to have the explicit transaction, it just isn't needed.

The code that we actually run in production has more than just one delete.

10/21/2003 9:30 AM | Tara

# re: Controlled DELETE

Hi Tara
I guess a problem exists:
what do you do if you have something like this?
declare @flag bit
set @flag=0
set rowcount 100
begin tran
delete from a where blah
if @@error <>0 set @flag=1
insert into b(......)
select ..... from x where blah
if @@error <>0 set @flag=1
if @flag=0
commit tran
else rollback tran
I think that works only for Delete and update ,Not insert .Am I right?

10/26/2003 1:45 AM | joseph

# re: Controlled DELETE

It would work for INSERTs as well. It just processes things a little at a time. So if you were inserting 1000000 rows from another table (maybe from a linked server or something), you could insert say 1000 rows at a time. You would have to delete the 1000 rows from the source server though each loop through though. I'm not sure INSERT is a good example of though of when to do things "controlled" though, but it would work.

10/28/2003 9:00 AM | Tara

# re: Controlled DELETE

Tara,

This was a great tip...I stumbled acros this usage a while back on SWYNK, and used it with a minor modification, to archive data from a table with nearly 100 million rows on a slow server which housed all of our reporting information. It had to be available during business hours, but the # of iterations it would complete durnig off-hours was unpredictable....so, I modified it by enclosing the entire proc in another loop, using WAITFOR TIME if the current time was during production hours. It took about 3 days to move all the data, but I didn't blow up the server due to transaction log space (in a data warehouse I could just blast all my transactions out of the log as soon as they were committed) and I didn't have to constantly monitor it.

2/23/2004 7:38 AM | crazyjoe

# re: Controlled DELETE

Probably overkill, but we record the elasped time for the delete and dynamically adjust the size of the
SET ROWCOUNT @DeleteThisMany
so that if the Server gets busy the delete reduces the effort, and if it finished quickly it adjusts to do more rows per loop. We also put a
WAITFOR DELAY '000:00:05'
at the bottom of the loop to allow other processes to run. Could also force a TLog Backup every N,000,000 rows if needed

9/26/2006 11:23 AM | Kristen

# re: Controlled DELETE

Yes that would be overkill for us.

9/26/2006 11:27 AM | Tara

Comments have been closed on this topic.

Ramblings of a DBA

My Links

Advertisement

News

Archives

Post Categories

Work