How to kill/resolve a reeeeally long-running update in SQL Server

Question

A colleague of mine (I promise it was a colleague!) has left an update running on our main SQL Server since last Thursday (yes that's right folks, we're pushing 100 hours now!). The SQL in question (in one transaction, I might add) is:

update daily_prices  set min_date = (select min(a.date)
   from daily_prices a       
   where a.key = daily_prices.key and       
   a.iid = daily_prices.iid)

(Yeah I know, heinous...)

The total cost in the query plan is coming out as 22186.7, the estimated number of rows to update is around 151 million.

We obviously need to resolve this query one way or another, we realise that if we are to kill the query we're going to generate some brutal rollback, but we've got no way of knowing how far it has gotten. The only thing we do know is this entry from sys.dm_exec_requests:

session_id  status      query_text              cpu_time    total_elapsed_time  reads       writes      logical_reads
52          suspended   update daily_prices...  2328469     408947075           13831137    42458588    151809497

So my question is, what would be our best course of action?

1. wait it out
2. kill it and roll back, and hope that it rolls back before the next ice age
3. something else?

Answer 1

I personally would want to wait it out unless I though it had no chance of finishing this week, the roll back at this stage could take far longer than the query has to date. If it's a production server, I really wouldn't take option 2 and kill it unless I absolutely had to.

In terms of regaining some control / working system if you have suitable backups, bring online another database restore the backup / tlog backups, but you will not want to restore to beyond when the transaction was started (or it will still have to roll it back.) This at least gives you a system you could continue dev work against, but unlikely to be the ideal situation for a prod system.

If it's a production server, have some kind words with the individual as to the suitability of testing queries and query plans prior to it being executed. I am sure many DBA's can suggest the less polite methods of instruction :)