Jun24 Written by:R Teachout
6/24/2008 8:01 PM
When deleting a huge table (such as when purging old transaction records of millions of rows) takes quite a long time and since DELETE is a fully logged operation....if something goes wrong during deletion the whole process will be rolled back which itself is a time consuming process.
This works on SQL Server 2005 statements like INSERT,UPDATE and DELETE.
By using the TOP operator, you can divide your time consuming operation into batches of small deletions like 5000 records at a time. The benefit of this approach is:
- Log space will be re-used again and again which will keep your log file size in control. (If your hosting company limits the size of your transaction log, you could add a dump tran log action after a batch! This would ensure your tran log wouldn't fill up)
- If operation fails at any time then only last batch will be rolled back and you can start purging again quickly after recovering from the failure.
Following is the TSQL code to perform batch deletions (this is an test example, see the bottom for a real usage example)
Use
YOURDB
Go
–Create a copy of Sales.SalesOrderHeader as a test tableSelect *into testOrderHeaderTable
from Sales.SalesOrderHeader
–Delete 5000 Rows at a timeWhile 1=1BeginDelete Top(5000) testOrderHeaderTable—Check for exit condition
If @@rowcount <5000 break
End
–Remove the test table
Drop table testOrderHeaderTable
RealLifeExample against a real table (Obviously, you should maybe test the above first?!!)
Use MyDb_db;
While 1=1
Begin
Delete Top(5000) orderhistory
If @@rowcount <5000 break
End
Tags: