All geek questions in one place

How to kill / allow re-run update in SQL Server

My colleague (I promise it was a colleague!) Left the update on our main SQL Server from last Thursday (yes, right-wing people, we push 100 hours!). SQL in question (in one transaction, I could add):

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)

(Yes, I know, disgusting ...)

The total cost of the query plan is issued as 22186.7, the estimated number of rows for updating is about 151 million.

We obviously need to resolve this request one way or another, we understand that if we want to kill the request, we are going to make some kind of brutal rollback, but we do not know how far it got. The only thing we know is the 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 the best course of action?

  • Wait.
  • kill him and roll away, and hope that he rolls back to the next ice age.
  • something else?
+3
source share
2 answers

Thus, we were tired of waiting for the completion of our transaction (after a full week, one piece of SQL, who would not do this?), And since this interfered with our backup process, we thought that killing was a necessary evil.

The database began to roll back the transaction.

5 days later.

, - , , "", *, , , , . , , , , , SQL Server hogging, , .

(* , , "", , , SQL Server, , SQL Server , )

, .

, . SQL Server 20 , ( , 25 , (!)).

/ , ( SQL Server , , ), , , ). , , ; - , .

, , , , , (, , , DBA). ? , , ! (epic Joel-Test !)

+2

, , , , . , 2 , .

- / , , /tlog, , ( .) , , , , prod.

, - . , DBA :)

+2

Source: https://habr.com/ru/post/1755478/

More articles:


All Articles