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.
>

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...
>
|||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...
>

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...
>|||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...
>

killing a process with a variable

I wish to kill all the processes for a given database.
have written the script that gives me all the spids for the database,
however I get an error when trying to execute;
KILL @.spid;
(Incorrect syntax near @.spid)
where @.spid is declared as a smallint.
Can anybody help?
TIA
Hi
You can't pass a variable. You need to create dynamic sql to execute that:
DECLARE @.exstring VARCHAR(20)
SELECT @.exstring = 'KILL ' + @.spid
executesql @.exstring
Regards
Mike
"Dan" wrote:

> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>
|||Hi Dan - I think this works.
DECLARE @.i INT
DECLARE @.strSQL NVARCHAR(255)
SET @.i = 73
SET @.strSQL = 'KILL ' + CAST(@.i AS CHAR (2))
--PRINT @.strSQL
EXEC sp_executesql @.strSQL
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
|||Hi,
If your Sql server version is 2000 then go for ALTER Database command rather
than KILL command.
ALTER Database <dbname> set single_user with rollback immediate
The above command will remove all the connected users to that database
immediately. After the activity u can change the db the multiuser.
ALTER Database <dbname> set multi_user
Thanks
Hari
MCDBA
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
>I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
|||Hi Dan,
I wrote the following script and tested it:
Use master
go
SET NOCOUNT ON
DECLARE @.strSQL varchar(255)
PRINT 'Killing Users'
PRINT '--'
CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = 'YOUR DATABASE NAME
HERE'
DECLARE @.spid varchar(10)
DECLARE @.dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @.spid
SET @.strSQL = 'KILL ' + @.spid
EXEC (@.strSQL)
END
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
PRINT 'Done'
go
Just replace 'YOUR DATABASE NAME HERE' with your database name.
Sasan
"Dan" wrote:

> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>

killing a process with a variable

I wish to kill all the processes for a given database.
have written the script that gives me all the spids for the database,
however I get an error when trying to execute;
KILL @.spid;
(Incorrect syntax near @.spid)
where @.spid is declared as a smallint.
Can anybody help?
TIAHi
You can't pass a variable. You need to create dynamic sql to execute that:
DECLARE @.exstring VARCHAR(20)
SELECT @.exstring = 'KILL ' + @.spid
executesql @.exstring
Regards
Mike
"Dan" wrote:
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>|||Hi Dan - I think this works.
DECLARE @.i INT
DECLARE @.strSQL NVARCHAR(255)
SET @.i = 73
SET @.strSQL = 'KILL ' + CAST(@.i AS CHAR (2))
--PRINT @.strSQL
EXEC sp_executesql @.strSQL
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>|||Hi,
If your Sql server version is 2000 then go for ALTER Database command rather
than KILL command.
ALTER Database <dbname> set single_user with rollback immediate
The above command will remove all the connected users to that database
immediately. After the activity u can change the db the multiuser.
ALTER Database <dbname> set multi_user
Thanks
Hari
MCDBA
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
>I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>|||Hi Dan,
I wrote the following script and tested it:
--
Use master
go
SET NOCOUNT ON
DECLARE @.strSQL varchar(255)
PRINT 'Killing Users'
PRINT '--'
CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = 'YOUR DATABASE NAME
HERE'
DECLARE @.spid varchar(10)
DECLARE @.dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @.spid
SET @.strSQL = 'KILL ' + @.spid
EXEC (@.strSQL)
END
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
PRINT 'Done'
go
--
Just replace 'YOUR DATABASE NAME HERE' with your database name.
Sasan
"Dan" wrote:
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>

Kill Users Without Processadmin Role

Hi folks, i create a procedure in master db that kill users spids. Giving EXECUTE permission on the procedure to users without giving processadmin role doesn't work! Any guidelines?
Howdy!Other than "Don't do that", nope.

Killing processes requires a lot more understanding of the server than the average user is ever likely to have. Our tech support people always want that ability, but I've never found any remotely safe way to give it to them because they don't think in terms of the impact that killing a process might have.

-PatP|||Teacher, it's the same LOCKING issue; The WELL DESIGNED VB APPLICATION timesout often coz of a table exclusively locked by one of the user; if the DBA isn't available; what to do then. This is not the solution but at least it would help the users to proceed.
No doubt, It's coz of the poorly designed tables; but i can't change all just once!

Howdy!|||I'll conceed that this is drastic, but if you have to give them something to fix the problem without having someone technically competent (a dba) to resolve the underlying problems on duty, then the best answer I've found is to give two people permission to reboot the database server.

If the entire machine (therefore the SQL Server) goes down, all of the database connections will be dropped at once. The resulting rollbacks will be handled by the recovery process when the SQL Service restarts after the reboot.

The total impact of a reboot will be large, but it will still be smaller than the probable impact of killing arbitrary spids. This isn't a good solution, but it is the best one I can offer if you can't have a dba that understands the issues on hand to resolve these problems.

-PatP