Skip to main content
Database Administrators

Return to Question

edited tags; improved link and attribution
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

Problem: I have a massive table - occupies a whopping 9.5 TBs. I need to delete around 5 mil rows from it. The best method I have come up with deletes 1 million records in 2 hours! Since I can not run this during peak time, I get only 2 hour window everyday and deleting 5 mil rows would take me 4 working days at this rate.

Question: I was wondering if SQL Server has a feature where I could run 2 or more deletes on a table simultaneously. Possibly something like virtually partitioning the table - not sure. 

I also want to add that running my script induces page locks but keeps the transaction log from growing. My script is very similar to the one suggested by vonPryz (:

http://datacentricity.net/2012/04/how-to-delete-from-large-tables-in-batches-without-growing-the-transaction-log/How to Delete From Large Tables in Batches Without Growing the Transaction Log) by Greg M Lucas.

Problem: I have a massive table - occupies a whopping 9.5 TBs. I need to delete around 5 mil rows from it. The best method I have come up with deletes 1 million records in 2 hours! Since I can not run this during peak time, I get only 2 hour window everyday and deleting 5 mil rows would take me 4 working days at this rate.

Question: I was wondering if SQL Server has a feature where I could run 2 or more deletes on a table simultaneously. Possibly something like virtually partitioning the table - not sure. I also want to add that running my script induces page locks but keeps the transaction log from growing. My script is very similar to the one suggested by vonPryz (http://datacentricity.net/2012/04/how-to-delete-from-large-tables-in-batches-without-growing-the-transaction-log/)

Problem: I have a massive table - occupies a whopping 9.5 TBs. I need to delete around 5 mil rows from it. The best method I have come up with deletes 1 million records in 2 hours! Since I can not run this during peak time, I get only 2 hour window everyday and deleting 5 mil rows would take me 4 working days at this rate.

Question: I was wondering if SQL Server has a feature where I could run 2 or more deletes on a table simultaneously. Possibly something like virtually partitioning the table - not sure. 

I also want to add that running my script induces page locks but keeps the transaction log from growing. My script is very similar to the one suggested by vonPryz:

How to Delete From Large Tables in Batches Without Growing the Transaction Log by Greg M Lucas.

Tweeted twitter.com/#!/StackDBAs/status/421287026097651712
Source Link
user32027
user32027
  • 137
  • 2
  • 2
  • 8

Running parallel delete statements

Problem: I have a massive table - occupies a whopping 9.5 TBs. I need to delete around 5 mil rows from it. The best method I have come up with deletes 1 million records in 2 hours! Since I can not run this during peak time, I get only 2 hour window everyday and deleting 5 mil rows would take me 4 working days at this rate.

Question: I was wondering if SQL Server has a feature where I could run 2 or more deletes on a table simultaneously. Possibly something like virtually partitioning the table - not sure. I also want to add that running my script induces page locks but keeps the transaction log from growing. My script is very similar to the one suggested by vonPryz (http://datacentricity.net/2012/04/how-to-delete-from-large-tables-in-batches-without-growing-the-transaction-log/)