August 17, 2015
Posted by on
Sometimes there will be the requirement to delete million rows from a multi million table, as it is hard to run a single Delete Statement Like below Query 1 because it could eventually fill up your transaction log and may not be truncated from log until all the rows have been deleted and the statement is completed because it will be treated as open transaction. Instead we can achieve the same by running the delete in multiple batches in an easy way. Once we knew the number of rows we need to delete, we will choose the batch size and number of batches we need to run like in Query 2 where I need to Delete to 1 million rows, I chose batch size as 10000 and number of batches to 100 , so that 10000*100 equals to 1 Million rows. Number of batches could be more approximately.
Query 1: Query to Delete records older than ‘1/1/2015’
Select Count(*) from History Where Date <= ‘1/1/2015’
1000000 – 1 Million rows and we need to delete all 1 million rows.
Delete from History Where Date <= ‘1/1/2015’
DELETE Top (10000) from History Where Date <= ‘1/1/2015’
The Tricky part is ‘Go ‘ which is batch separator , the number we pass to the Go is the number of times you want to run that Query and each run time is considered as single batch.