April 9, 2008 at 5:02 pm
I was trying to delete a DB table that had over 235 mill records, it was taking over 2 hours to do so. So, i canceled the query, since i wanted to use the DB for other work. However, over an hour has passed and Management studio says "Cancelling query". What should i do to end it immediately ?
April 9, 2008 at 5:13 pm
next time try TRUNCATE if you are wiping out the whole table.
if you save and close the query window in SSMS, it will usually respond to that.
---------------------------------------
elsasoft.org
April 9, 2008 at 5:13 pm
Would a drop table be quicker?
April 9, 2008 at 5:15 pm
Yes..i guess so, however in the past i have done that, the process seems to keep executing in the background though, consuming resources and the only way to end it then would be to restart the SQL server, which puts it in recovery mode 🙁
April 9, 2008 at 5:16 pm
yes, drop is fast too.
delete is slow because the whole mess is logged to the transaction logs.
I'm afraid you'll just have to wait this time because the server is busy rolling back all the rows it deleted.
---------------------------------------
elsasoft.org
April 9, 2008 at 5:32 pm
Thankyou for your suggestion,the query cancelled, i truncated the table and it took just 1 second (haha!) .
It also keeps the schema, so no need to recreate 🙂
April 9, 2008 at 5:36 pm
Do i need to recreate the index?
April 9, 2008 at 5:50 pm
if you are loading the table again with 200m rows, it's faster to drop the index, load the table, then recreate the index.
also, I hope you are using BCP or similar to load this table...
---------------------------------------
elsasoft.org
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply