Killing SPIDs

Is there any other way to kill a process besides using kill?
I have a spid that will not die..and has been in the killed/rollback for
roughly 2hrs with the current wait reason as Waiting on OLEDB provider
The procedure the spid is calling uses an opendatasource to a Oracle server.Hi,
KILL is the only command available to remove a partcular SPID from SQL
Server. To reove all the connections connected to a database use
alter database <dbname> set single_user with rollback immediate
To get the status of KILL command you could use :-
KILL <SPID> WITH STATUSONLY
WITH STATUSONLY
Specifies that SQL Server generate a progress report on a given spid or UOW
that is being rolled back. The KILL command with WITH STATUSONLY does not
terminate or roll back the spid or UOW. It only displays the current
progress report.
Thanks
Hari
SQL Server MVP
"Gary" <clgary@.yahoo.com> wrote in message
news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Is there any other way to kill a process besides using kill?
> I have a spid that will not die..and has been in the killed/rollback for
> roughly 2hrs with the current wait reason as Waiting on OLEDB provider
> The procedure the spid is calling uses an opendatasource to a Oracle
> server.
>|||Hi
With linked servers, stop MSDTC and re-start it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Gary" <clgary@.yahoo.com> wrote in message
news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Is there any other way to kill a process besides using kill?
> I have a spid that will not die..and has been in the killed/rollback for
> roughly 2hrs with the current wait reason as Waiting on OLEDB provider
> The procedure the spid is calling uses an opendatasource to a Oracle
> server.
>|||It's not setup as a linked server per se, it just uses OPENDATASOURCE.
However, I stopped MSDTC to see if that would make it stop, and it didnt.
Anyone have any other suggestions, before I restart the service? It's on a
production box and I'd rather not
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23QLdo9taFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> With linked servers, stop MSDTC and re-start it.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Gary" <clgary@.yahoo.com> wrote in message
> news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
>> Is there any other way to kill a process besides using kill?
>> I have a spid that will not die..and has been in the killed/rollback for
>> roughly 2hrs with the current wait reason as Waiting on OLEDB provider
>> The procedure the spid is calling uses an opendatasource to a Oracle
>> server.
>|||OPENDATASOURCE in a SQL Agent Job? A stored procedure? Ad hoc T-SQL from a
user or a remote process? Or, are you using DTS?
Chances are that SQL Server had to "go preimptive" and spawn an external
thread. You could check the task list and kill whatever thread was hung
keeping the KILL from ROLLING BACK.
Sincerely,
Anthony Thomas
"Gary" <clgary@.yahoo.com> wrote in message
news:OwXadQuaFHA.1456@.TK2MSFTNGP15.phx.gbl...
It's not setup as a linked server per se, it just uses OPENDATASOURCE.
However, I stopped MSDTC to see if that would make it stop, and it didnt.
Anyone have any other suggestions, before I restart the service? It's on a
production box and I'd rather not
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23QLdo9taFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> With linked servers, stop MSDTC and re-start it.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Gary" <clgary@.yahoo.com> wrote in message
> news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
>> Is there any other way to kill a process besides using kill?
>> I have a spid that will not die..and has been in the killed/rollback for
>> roughly 2hrs with the current wait reason as Waiting on OLEDB provider
>> The procedure the spid is calling uses an opendatasource to a Oracle
>> server.
>