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
Legacy Comments
|
DavidM
2003-10-15 |
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 |
|
Tara
2003-10-15 |
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. |
|
Stoad
2003-10-16 |
re: Controlled DELETE Very neat!! |
|
robvolk
2003-10-16 |
re: Controlled DELETE Graz? |
|
Tara
2003-10-16 |
re: Controlled DELETE Damn, my last comment should say: but it wouldn't hurt to make them better. |
|
Satish Balusa
2003-10-17 |
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. |
|
Tara
2003-10-20 |
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. |
|
joseph
2003-10-20 |
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 |
|
Ashutosh Nilkanth
2003-10-21 |
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. |
|
Tara
2003-10-21 |
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. |
|
joseph
2003-10-26 |
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? |
|
Tara
2003-10-28 |
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. |
|
crazyjoe
2004-02-23 |
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. |
|
Kristen
2006-09-26 |
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 |
|
Tara
2006-09-26 |
re: Controlled DELETE Yes that would be overkill for us. |