3

I am deleting 257 million rows out of a table in SQL Server 2008. I can't truncate it. I probably could have done it smarter though.

2 hours into the delete, the log file grew and I am out of disk space. The query is still running.

What should I do? Will the delete succeed?

3

Wait for the tx to roll back, the delete will not succeed. Or load a backup - could be faster.

Either delete in increments (10.000 at a time) or make sure your server is adequate to handle your needs. All that CAN be done there.

3

How many records do you need to retain in the table compared to 257 million deletes? If that number is smaller then insert those records in batches to a new table, switch the tables around using ALTER TABLE SWITCH and drop the older table.

http://jahaines.blogspot.com/2009/12/sql-server-2005-how-to-move-10-millions.html

Another interesting idea is to setup partitioning and drop the older partitions.

http://blogs.msdn.com/b/bartd/archive/2010/06/01/purging-data.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.