KILL command failed - 6114 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

KILL command failed - 6114

Author

Topic

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-19 : 10:19:01

We have a transaction that has been running for 1 month+ now

Unnoticed, until now when we started with some schema changes.
And it has gotten in our way!

exec sp_who2 -- part of result
SPID  Status                         Login                          HostName BlkBy DBName        Command
----- ------------------------------ ------------------------------ -------- ----- ------------- ----------------
64    sleeping                       xxxxxxxx                       myhost99  56    DBXX         DROP TABLE

kill 56
------------------------------------------------------------------------------------
SPID 56: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

Yeah right it's been rolling back for a month now !

select req_spid,req_transactionUOW from master..syslockinfo where req_spid = 56
req_spid    req_transactionUOW                   
----------- ------------------------------------ 
56          00000000-0000-0000-0000-000000000000
56          50A33CD6-C9E0-44CA-B8A7-1B50F953778E
56          50A33CD6-C9E0-44CA-B8A7-1B50F953778E

Ok, some distributed stuff...
kill '50A33CD6-C9E0-44CA-B8A7-1B50F953778E'
------------------------------------------------------------------------------------
Server: Msg 6114, Level 16, State 1, Line 1
Distributed transaction with UOW {50A33CD6-C9E0-44CA-B8A7-1B50F953778E} is being used by another user. KILL command failed.


exec sp_lock -- part of result

spid   dbid   ObjId       IndId  Type Resource         Mode     Status 
------ ------ ----------- ------ ---- ---------------- -------- ------ 
56     10     0           0      DB                    S        GRANT
56     12     0           0      DB                    S        GRANT
56     12     409104548   0      TAB                   Sch-S    GRANT

How do we KILL the sucker?, we want process #56 DEAD!

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-19 : 10:29:12

RESOLVED - Restarted SQL Server.
(That was one tough spid!)

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-19 : 10:33:26

Did you check if there was any blocking of 56? 56 was blocking another....but what about anything block 56.

Brett

8-)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-19 : 10:39:24

quote:
That was one tough spid!

I think Jeff has got a good title for a sequel (...or would that be a SQL?)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46653&SearchTerms=deadlock

Be One with the Optimizer
TG

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-19 : 10:39:51

No, process #56 was the sole offender.
It had a Sch-S lock, and I was dropping the table, the process had been running since 20050314.
I am not sure what was going on...

rockmoose

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-19 : 10:46:16

I have seem this happen where a proceess gets in a rollback state, but never seems to finish.

I usually just find a down time window and restart SQL Servr. Or just restart it if it is really causing a problem.

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-19 : 10:58:19

That was a very good thread TG

, I remember it !!!

Yes Michael, we did a restart, and the problem was solved.

(#56 hid behind a undercover transaction)

rockmoose

ssivaprasad
Starting Member

9 Posts

Posted - 2006-05-05 : 23:00:24

I too facing this problem. Why is that KILL is not helping with this situation? KILl with Statusonly results that rollback 100% completed in 0 seconds.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources