| Author |
Topic |
|
PatDeV
Posting Yak Master
197 Posts |
 Posted - 2006-06-02 : 09:51:28
|
| Hi allI am trying to kill @spidbut it does not work.it returns thisSPID 124: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.But the process is still running and did not finished at all!!What is the alternat way to do it.Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-02 : 09:54:50
|
| It's saying that the spid is rolling back. That it's completed without dying makes me think that maybe it's waiting for an external response before it can die - bcp, xp_cmdshell, xp_sendmail, linked server, ...==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
PatDeV
Posting Yak Master
197 Posts |
Posted - 2006-06-02 : 10:05:36
|
| How can i clear that then!!Please helpThanks |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-06-02 : 10:09:21
|
| You could try restarting the server if that's an option.-------Moo. :) |
|
|
|
PatDeV
Posting Yak Master
197 Posts |
Posted - 2006-06-02 : 10:19:09
|
| no can't do that in middle of day or at night too!! |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-06-02 : 10:25:24
|
| I don't believe that there is another way of sorting it.-------Moo. :) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-02 : 10:27:56
|
| I have never seen anything fix it, except for restarting SQL Server.CODO ERGO SUM |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-06-02 : 11:00:54
|
| If it helps at all, I think you just need to restart the (sql server) service rather than the whole box.Be One with the OptimizerTG |
|
|
|
PatDeV
Posting Yak Master
197 Posts |
Posted - 2006-06-02 : 11:08:55
|
| I will have to try that.thanks for the help. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-02 : 11:21:53
|
| If it's waiting on an external process like bcp then you can kill the bcp thread in windows.If it's a result from a linked server then kill the spid on the linked server.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
PatDeV
Posting Yak Master
197 Posts |
Posted - 2006-06-02 : 11:26:31
|
| I tried that too and killed users on Linked server too but it still not working on the main server.and how can in kill bcp in windows?Thanks |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-02 : 11:38:52
|
| Look at the processes there would be a bcp process there.You need to find what your spid is doing.try dbcc inputbuffer (spid)and DECLARE @sql_handle binary(20) SELECT @sql_handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = @blkspid AND ecid = 0 SELECT * FROM ::fn_get_sql(@sql_handle)set @blkspidand see if they tell you anything.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
PatDeV
Posting Yak Master
197 Posts |
Posted - 2006-06-02 : 13:53:09
|
| For all SPID the SQL Handleris like0x0000000000000000000000000000000000000000 |
|
|
|
|
kill User