November 17, 2013 at 12:34 am
Hi,
I have a questions related to KILLing a transaction.
1. Can i directly Kill a transaction if I found it is the culprit in the blocking chain. Do I need to intimate the end-users of Killing the head blocker. What are the considerations to be taken care before I Kill a transaction and what are the side-effects of a transaction based on DML or SELECT. If it is SELECT can I directly KILL it.
2. If I killed a DML transaction, how long the locks will be held. Are they going get released immediately. For instance. In sql 2000, I started a DTS package job and after sometime I stopped in the middle thinking that it is running slow or I missed a pre-req step which is needed before kicking of DTS job (for instance , i need to run a house keeping sql script before I start running the DTS job). Is there any change in sql 2000 based locking and sql 2005 and later versions or do I need to restart sql server to release the locks.
3. How can I tell how long my KILL command is going to take or how much work % it has completed.
November 17, 2013 at 2:03 am
1) you can, but you should probably figure out what it's doing first and what will happen when it gets undone.
2) Until the rollback completes. Do not restart SQL unless you want the entire database unavailable until the rollback completes
3) Generally longer than the transaction has been running.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2013 at 4:06 am
Hi,
Below are the steps i normally follow..
1. I check is there any blocks through sp_who2 (check BlkBy column)
2. if there any blocks, check both SP ID (SPID & BlkBy SP Id's) what exactly they are executing through DBCC INPUTBUFFER(@SPID).
3. Decide which SP Id to kill (not advisable to kill any transactions on production).
4. Now you've killed the SPID, you can monitor its rollback progress by issuing KILL @SPID WITH STATUSONLY.
hope this helps..
November 17, 2013 at 4:08 am
Thanks for the help.
November 17, 2013 at 4:23 am
Hi,
Would like to know is there any particular scenario wherein If I had to Kill a process/spid and thinking that the locks are released but it didn't but I had to start the workload again, it is again slow due to the fact that the locks are still retained. Can anyone share such instances/examples where locks are being hold and what would you do to overcome the problem. Someone has already killed it and comes to dba for help !! I agree with Gail Shaw that we have to wait until the rollback finished and nothing much can be done. Are they any alternate ways to tackle
@Gail, mentioned that Kill/rollback takes much longer time longer than the transaction has been running. Is that because of the fact that rollback is single threaded process and original query might have run using parallel threads. I came across the blog of Bob Dorr from Microsoft CSS team.
Also, why can't rollback process be parallelised? Any technical reason behind it?
November 17, 2013 at 10:06 am
Oracle_91 (11/17/2013)
Can anyone share such instances/examples where locks are being hold and what would you do to overcome the problem. Someone has already killed it and comes to dba for help !!
You wait until the rollback has completed, at which point all locks the session holds will be released. There's not much else you can do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2013 at 11:39 am
Okay. Thank you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply