I need to delete about 900m rows from a table that has 6b rows. Since big deletes cause big problems for the transaction log, I'm deleting the data in small batches. What puzzles me is SQL Server does not like to commit each chunked delete.
Let's say I delete it in batches of 1m rows:
DECLARE @N int = 1000000 DELETE TOP (@N) FROM my_table WHERE ...
In a separate query, I monitor the status of the delete:
-- Before executing the first query sp_spaceused 'my_table' -- row_count = 6b -- During execution sp_spaceused 'my_table' -- row_count = decreasing from 6b -> 5.999b
And then it just hung! I can tell that 1m rows have been removed from
my_table by peeking at the row count, but the first query just keeps going on and on.
What could cause this behavior? Another big table has a Foreign Key relationship referencing
my_table.ID. Could FK validation be the culprit here?
Database is SQL Server 2012.