Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up.
Sign up to join this community
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?
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.
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
