I was wondering if I can get some pointers on the strategies involved in deleting large amounts of rows from a table. The table is a highly transactional table in a OLTP environment and we need to delete 34 million rows from it. I thought about writing out the keys to another table and then deleting during slow hours in batches. Any other thoughts, idea's would be appreciated. Thanks
If your table is not partitioned, then the deletion in batches in the easiest and probably preferable way. In case the table is partitioned then there could be basically 3 scenarios: 1. All data in a partition needs to be deleted - simply switch out the partition to a temp table and drop 2. Most of the data needs to be delete from partition - switch out the partition, move correct data to other temp table, drop original switched out partition and switch in partition with correct data. 3. Some of the data needs to be deleted from partition - delete data in batches. **EDIT:** An example of DELETE loop with 2 seconds delay between loops: DECLARE @cnt int; SET @cnt = 1; WHILE @cnt > 0 BEGIN DELETE TOP (1000) aTable WHERE condition SET @cnt = @@ROWCOUNT WAITFOR DELAY '00:00:02' END
SET ROWCOUNT 10000 --DECLARE @Text varchar(255) --SET @Text = 'Deleted 10000 rows' DELETE FROM [database].[dbo].[table] WHERE DateChecked < '2011-01-01' WHILE @@rowcount > 0 BEGIN DELETE FROM [database].[dbo].[table] WHERE DateChecked < '2011-01-01' --PRINT (@Text) END SET ROWCOUNT 0 GO
I do something similar to the earlier posts, but I also add t-log backups. I create a separate counter, for example every 50 batches I run a t-log backup. Experiment a bit to see how frequently you want to run it.