Deleting thousands of registries 3

[tommyspain]

I cannot delete (10000000) registries. I tried
DELETE FROM stat WHERE idx=62
It sends timeout and the proccessor stays on 100% during so long.
I guess there is another way to do it.

Thanks in advance.

 

[donutman]

You need to do them in batches using "Set RowCount 10000" or so. You can create a loop until @@RowCount is <10000 so that you know you are done.
-Karl

 

[SQLSister]

How to Use Batch Size to Speed Mass Updates, Inserts and Deletes faq183-3141

THis FAQ may help.

 

[RobertT687]

Agreed. Break the job up into batches. Also, make sure you have sufficient disk space for your transaction log. It's bound to grow quite a bit deleting 10000000 rows of anything.
Another thought. What portion of the total table contents is represented by the 10000000 rows? If it is most of the contents, consider a SELECT * INTO statement to create an identically structured table, drop the original, rename the new table to the old name, then recreate indexes, views and other objects. Could be a time saver and would save log space.

 

[tommyspain]

Thanks guys!!