June 2, 2005 at 2:27 pm
I have a process that I killed but is rolling back forever. If I rerun the kill WITH STATUSONLY for that process I get:
SPID 60: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
I run sp_lock 60 and I get the following:
60 20 0 0 DB S GRANT
60 20 0 0 DB S GRANT
60 2 0 0 DB S GRANT
60 20 1263343565 0 TAB Sch-S GRANT
60 20 1263343565 0 TAB [COMPILE] X GRANT
The object 1263343565 is a stored procedure.
Can anybody explain me what kind of lock is that and if there is any way to release the lock without restarting the SQL server?
KILL is not working as the process is already killed and never finishes the rollback.
June 2, 2005 at 2:36 pm
It is an exclusive (X) table (TAB) lock. This means, that a transaction has occured; INSERT, UPDATE, DELETE on a table the the stored procedure is referencing. If you run kill a second time, it will give you the status. I am not sure how much data this procedure effects, but it will rollback. Even if you restart the service, when the databases are restarted and recovered, the database will issue a rollback. There is no way around the long running rollback. In order for your database to have integrity, the rollback will occur one way or the other. The kill should work. You can always restart the services, but the rollback will restart.
Thanks
Greg
June 2, 2005 at 2:40 pm
Does that mean that we can't rely on this information??
"Estimated rollback completion: 100%. Estimated time remaining: 0 seconds."
June 2, 2005 at 3:15 pm
It is Microsoft! Can you ever rely on anything they produce that determines length of time? I would say, NO. You can't follow the seconds for the Kill. Have you ever copied a large file and seen the seconds go into the hundreds of thousands.
What does sp_who2 show for activity? You should see CPU and Disk I/O while the rollback is occuring for your SPID. It should not be SLEEPING, but ROLLBACK.
Thanks
Greg
June 2, 2005 at 3:18 pm
Hehe... you seem to have more experience than me under this topic.
June 2, 2005 at 3:18 pm
The problem was fixed once the server was restarted. However, we cannot always restart the server. I would like a solution less dramatic.
Gabriela
June 2, 2005 at 3:32 pm
I think I read that a little too quick. I must have read that as 100%, 0 Sec. as 0% 100 Sec. I know that the time MS produces is usually not correct. I have seen it too many times. Nonetheless, That SPID should not have hung. That is weird in its self.
Thanks Remi, You made me take a double take.
Greg
June 3, 2005 at 1:43 am
Greg: Actually, it is not an exclusive table lock. It is a compile lock for compiling an execution plan for the stored procedure. The lock above it is a Schema-Stability lock that prevents DDL statements from changing the schema of the object.
Gabriela: Unfortunately I have no idea why the procedure hung. What does the procedure do? Could you post the code?
June 6, 2005 at 1:42 am
Hi,
Had this once myself. Turned out to be a distributed tran. Stopped and restarted ms DTC and problem solved.
hth
JP
June 9, 2005 at 5:27 am
I have had this a few times. I tried restarting MSDTC and that didnt work. It seems to happen for me with external procedure calls (xp_cmdShell, dtsrun etc). I think sql might be waiting for feedback form the killed pid. obviously it will never get any feedback as it cant roll external commands back etc. restarting the service always works.
has any1 else come across this.
June 9, 2005 at 9:38 am
It seems that my problem was the presence of distributed transactions. Unfortunately, I restarted the server before trying to restart the MSDTC, so for now I cannot confirm that restarting the MSDTC would have done any good.
I wil keep it mind, though, and next time I will try this before trying any server restart.
Thank you all, people, for your input.
Gabriela
June 28, 2005 at 5:14 pm
Yes, I am seeing the same issue. Found this thread with the help of Google searching for
"transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds."
I've tried restarting DTC but that doesn't work for me. yes, the stored proc in question is accessing a COM object using sp_OACreate command. Basically it looks like this (but with more code for error handling and variable declarations:
EXEC @hr = sp_OADestroy @object
Any ideas on how to get the spids out of kill/rollback without restarting? I won't be able to restart the server until after end of month/end of quarter without getting fired. 😉
March 20, 2006 at 2:28 pm
kill Dtsrun.exe on the server
Amit Lohia
March 13, 2008 at 3:01 pm
I am having the same issue with a sproc.
I cant drop/modify a sproc
this sproc is inserting a remote data set (using an oracle linked server) into a local table.
Sproc called using OSQL.exe, it was taking too long. so, ctrl+break 'ed the process but the spid still shows up in the sp_who2
Kill 54 gives this result:
SPID 54: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
sp_who2 gives me the following result:
SPID Status Login HostName BlkBy DBName Command
54 RUNNABLE xyz123 workstn2 . DW KILLED/ROLLBACK
CPUTime DiskIO LastBatch ProgramName SPID REQUESTID
0 7 03/12 09:15:48 OSQL-32 54 0
sp_locks :
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
54 12 0 0 DB S GRANT
54 12 0 0 MD 4(1:0:0) Sch-S GRANT
54 12 60 1 KEY (7b0030f436bd) S GRANT
54 12 2052202361 0 TAB Sch-S GRANT
54 12 2052202361 0 TAB [COMPILE] X GRANT
54 12 60 1 RID 1:1583561:0 S GRANT
54 12 60 1 PAG 1:1583561 IS GRANT
I tried "Kill UOW" (you can get the UOW(Unit Of Workid) using "select distinct req_transactionUOW from sys.syslockinfo where req_spid = 54")
kill '00000000-0000-0000-0000-000000000000' results in:
Msg 6110, Level 16, State 1, Line 1
The distributed transaction with UOW {00000000-0000-0000-0000-000000000000} does not exist.
I tried restarting the DTC in services, didnt work.
Next alternative seems to be restarting the SQL service or server.
how else can I kill this process, other than restarting? what other avenues did you guys take to get this issue fixed.
Thanks
Shiva
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 3, 2009 at 3:34 am
We just had a similar problem here. I can confirm that stopping and restarting MSDTC finally got rid of the process.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply