Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
    
This is indeed useful information. Thanks –  bladefist Jun 20 '11 at 13:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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