Hi.
I have run xp_cmdshell from within a stored procedure, and it has hung. Is
there any way to kill the process, other than rebooting SQL Server?
Cheers
NeilIf you do not have luck using "kill spid", go to the server and end the tas
k
using "task manager".
AMB
"NeilDJones" wrote:
> Hi.
> I have run xp_cmdshell from within a stored procedure, and it has hung. Is
> there any way to kill the process, other than rebooting SQL Server?
> Cheers
> Neil
Showing posts with label kill. Show all posts
Showing posts with label kill. Show all posts
Wednesday, March 28, 2012
Killing timed out connections
I am having a problem with an application that does not kill timed out connections. This is normally not an issue, but when something causes the timed out connections to build up, it stops the frontend from working correctly. The frontend developers are trying to figure out how to change their code to check for and drop timed out connections at the application. Until then, I need a way to check for timed out connections at the database and drop them there via a job that will run every 10 minutes or so. I have to make sure that only timed out connections are dropped and not active ones. Any suggestions?
-SQLBill
Sorry, SQL Server simply does not know what that is. A time out occurs in your connection object - ODBC, OLEDB... SQL Server has no concept of a timeout, so it would not know if an application on the other side has simply given up waiting for a response. What your developers need to do within the code is to issue a reset connection when they go to grab a connection and use it. That ensures that any resources are released. They need to issue a reset anytime they issue a request, grab a connection from a pool, or return a connection to a pool.sqlkilling the duplicates from a table using sql
hi, what is a good way to kill the duplicates from a table. when i say killing duplicated, i mean killing all the rows for the repeated row.
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t1 values (3)
insert into #t1 values (4)
insert into #t1 values (4)
insert into #t1 values (5)
insert into #t1 values (5)
WorkTempID ItemNo Seq
100196 RTP-22 1
100197 RTP-22 2
100198 RTP-22 3
100199 RTP-22 3
100200 RTP-22 4
100201 RTP-22 4
100202 RTP-22 5
100203 RTP-22 5
********************************************************
see how Seq 3, 4 and 5 are repeated? so for the output i want.
WorkTempID ItemNoSeq
100196RTP-221
100197RTP-222
********************************************************
i DO NOT want this as the output. i already know how to achive this using DISTINCT keyword
WorkTempID ItemNoSeq
100196RTP-221
100197RTP-222
100198RTP-223
100200RTP-224
100203RTP-225How big is the table?|||not that big.. few dozen rows. i am basically getting the job done by reading through the whole table, doing a count based on seq. if the count is more than 1, i update that row with "delete" as the ItemNo. at the end just deleting everyting that has "delete" for ItemNo. gets the job done but i think there is a better way to do this.|||Assuming that you actually want to remove the duplicates from the underlying table...
If the table isn't that big then consider something like this:
Declare @.tblTemp table (WorkTemplID int, Item char(10), Seq int)insert into @.tblTemp
select distinct * from Itemstruncate Items
insert into Items
select * from @.tblTemp
Not the most elegant code but very easy to understand|||thanks for the reply but it does not give me what i need. remember, i not only need to kill the duplicates but also the orginal row that is duplicated. if seq 3 is repeated 5 times DISTINCT keyword will give me 1 row that has seq 3 in it. but i dont want to get ANY rows with seq 3.|||create table #t1 (c1 int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t1 values (3)
insert into #t1 values (4)
insert into #t1 values (4)
insert into #t1 values (5)
insert into #t1 values (5)
select * from #t1
group by c1
having count(c1) < 2|||aaah. Ok you want something like this...
|||create table #t1 (c1 int)
delete <table>
from <table> ORG
inner join
(select <col1>, <col2>,etc from <table> group by <col1>, <col2>,etc
having count(*) > 1) <some table alias STA> on
STA.<col1> = ORG.<col1> and etc (for all cols)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t1 values (3)
insert into #t1 values (4)
insert into #t1 values (4)
insert into #t1 values (5)
insert into #t1 values (5)
select *
into #t2
from #t1
group by c1
having count(c1) < 2
select * from #t2|||ok ok, here's my last go at a perfect template ...
create table #t1 (c1 int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t1 values (3)
insert into #t1 values (4)
insert into #t1 values (4)
insert into #t1 values (5)
insert into #t1 values (5)
delete from #t1
where c1 in
(
select c1
from #t1
group by c1
having count(c1) > 1
)|||ok, my last attempt at making the perfect template for this ...
create table #t1 (c1 int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t1 values (3)
insert into #t1 values (4)
insert into #t1 values (4)
insert into #t1 values (5)
insert into #t1 values (5)
delete from #t1
where c1 in (
select c1
from #t1
group by c1
having count(c1) > 1
)
select * from #t1
Richard101|||Richard101 that only works if you've got a single unique column. The original example has no unique key columns...wouldn't have a problem if it did.
I just want to see you write out a few more templates :)|||ok, although my idea of a template is something that works, reduced to it's minimum, that you can build up.
right, using your data...
--
create table #t1 (c1 varchar(10), c2 varchar(10), c3 int)
insert into #t1 values ('100196', 'RTP-22', 1)
insert into #t1 values ('100197', 'RTP-22', 2)
insert into #t1 values ('100198', 'RTP-22', 3)
insert into #t1 values ('100199', 'RTP-22', 3)
insert into #t1 values ('100200', 'RTP-22', 4)
insert into #t1 values ('100201', 'RTP-22', 4)
insert into #t1 values ('100202', 'RTP-22', 5)
insert into #t1 values ('100203', 'RTP-22', 5)
delete from #t1
where c3 in
(
select c3
from #t1
group by c3
having count(c3) > 1
)
select * from #t1
--
Richard101|||Teehee. I was assuming that that a duplicates had to be c1 AND c2 AND c3. My fault. So how would you write that one Richard? ;)
killing sqlservr.exe from sqlclr code
I keep getting different answers from different people on regarding if you can or cannot kill the hosting sql server process with an unsafe assembly. Can you do this? If so could you please attach a sample demonstrating this?
Thanks,
Derek
Uhh, delicate question. Sure Microsoft did everything to prevent this. I know that in beta this was sort of unstable, but right now I have no clue how to do this.
If someone has an example to do this, this should be reported as a bug to Microsoft in order to prevent this. But I would assume that the CLR hosting process is quite stable right now.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
You can always write an XP to do the same anyway for instance.|||could you please give me some quick code in C#/VB.Net that will kill the hosting process in that case?|||I am only concerned about this becaseu I am a lead author on a sqlclr book.|||
Is this simple enough?
public static void killsql()
{
System.Environment.Exit(-1);
}
Steven
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.
>
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...
>
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...
>
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 Sleeping Processes
Is there a way to Kill all 'sleeping' processes at once ?
Thanks.Hi,
Do not take a risk in killing all the sleeping process . But the way is,
select 'kill '+convert(char,spid) +char(10)+'go' from master..sysprocess
where status like 'sleep%'
Execute the output of the above script in query analyzer. This will kill all
the sleeping process
Thanks
Hari
MCDBA
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:5e2a01c3e5b3$358bb1c0$a401280a@.phx.gbl...
But the way is,
master..sysprocess
This will kill all
message
Thanks.Hi,
Do not take a risk in killing all the sleeping process . But the way is,
select 'kill '+convert(char,spid) +char(10)+'go' from master..sysprocess
where status like 'sleep%'
Execute the output of the above script in query analyzer. This will kill all
the sleeping process
Thanks
Hari
MCDBA
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:5e2a01c3e5b3$358bb1c0$a401280a@.phx.gbl...
quote:|||Thanks.......
> Is there a way to Kill all 'sleeping' processes at once ?
> Thanks.
quote:
>--Original Message--
>Hi,
>Do not take a risk in killing all the sleeping process .
But the way is,
quote:
>select 'kill '+convert(char,spid) +char(10)+'go' from
master..sysprocess
quote:
>where status like 'sleep%'
>Execute the output of the above script in query analyzer.
This will kill all
quote:
>the sleeping process
>Thanks
>Hari
>MCDBA
>
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
quote:sql
>news:5e2a01c3e5b3$358bb1c0$a401280a@.phx.gbl...
once ?[QUOTE]
>
>.
>
Killing Sleeping Processes
Is there a way to Kill all 'sleeping' processes at once ?
Thanks.Hi,
Do not take a risk in killing all the sleeping process . But the way is,
select 'kill '+convert(char,spid) +char(10)+'go' from master..sysprocess
where status like 'sleep%'
Execute the output of the above script in query analyzer. This will kill all
the sleeping process
Thanks
Hari
MCDBA
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:5e2a01c3e5b3$358bb1c0$a401280a@.phx.gbl...
> Is there a way to Kill all 'sleeping' processes at once ?
> Thanks.|||Thanks.......
>--Original Message--
>Hi,
>Do not take a risk in killing all the sleeping process .
But the way is,
>select 'kill '+convert(char,spid) +char(10)+'go' from
master..sysprocess
>where status like 'sleep%'
>Execute the output of the above script in query analyzer.
This will kill all
>the sleeping process
>Thanks
>Hari
>MCDBA
>
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5e2a01c3e5b3$358bb1c0$a401280a@.phx.gbl...
>> Is there a way to Kill all 'sleeping' processes at
once ?
>> Thanks.
>
>.
>
Thanks.Hi,
Do not take a risk in killing all the sleeping process . But the way is,
select 'kill '+convert(char,spid) +char(10)+'go' from master..sysprocess
where status like 'sleep%'
Execute the output of the above script in query analyzer. This will kill all
the sleeping process
Thanks
Hari
MCDBA
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:5e2a01c3e5b3$358bb1c0$a401280a@.phx.gbl...
> Is there a way to Kill all 'sleeping' processes at once ?
> Thanks.|||Thanks.......
>--Original Message--
>Hi,
>Do not take a risk in killing all the sleeping process .
But the way is,
>select 'kill '+convert(char,spid) +char(10)+'go' from
master..sysprocess
>where status like 'sleep%'
>Execute the output of the above script in query analyzer.
This will kill all
>the sleeping process
>Thanks
>Hari
>MCDBA
>
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5e2a01c3e5b3$358bb1c0$a401280a@.phx.gbl...
>> Is there a way to Kill all 'sleeping' processes at
once ?
>> Thanks.
>
>.
>
Killing Remote Application
Question:
I want to restrict a user from logging in different workstations or creating a multi-sessions of my application. HOw can I remotely kill the application if he tries to login again considering that he has still live connection or open application in the same pc or another? Assuming the user has successfully logged on to the other pc, how can I send a message to his original opened application informing that connection is closed or something like.
If you are familiar with Yahoo Messenger, you will understand my point.
Im using VB6 and MSSQL 2000...
Anybody who has an answer for this please help. As administrator, we normally prevent users from opening different sessions.
Assuming I can kill a live connection using KILL (sp_id) in SQL Server, In VB app, how can I test the connection if its alive or not coz I might use a timer to check every second or a minute so a message box will appear saying that connection is killed remotely and the application terminates?
declare @.@.user_id varchar(20)
set @.@.user_id='TheUserIDLogged'
sp_who @.@.user_id
I also tried to do this but error occurs:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@.@.user_id'.
How can i get the sp_id of this connection so that I can execute the
KILL function?
Any relevant idea is highly appreciated...ThanksHow do they login?
What's your security model?
And if you find multiple logins, which one do you pick to kill (Batai?)
USE Northwind
GO
CREATE TABLE tbl_sp_Who2 (
SPID int
, Status varchar(255)
, Login varchar(255)
, HostName varchar(255)
, BlkBy varchar(255)
, DBName varchar(255)
, Command varchar(255)
, CPUTime int
, DiskIO int
, LastBatch varchar(255)
, ProgramName varchar(255)
, SPID2 int)
GO
INSERT INTO tbl_sp_Who2 EXEC sp_who2 active
SELECT *
FROM tbl_sp_Who2
WHERE Login IN ( SELECT Login
FROM tbl_sp_Who2
GROUP BY Login
HAVING COUNT(*) > 1)
GO
DROP TABLE tbl_sp_Who2
GO
HTH|||After opening the initial connection, I would test to see if this user already has open connections using the sp_who command passing the login. If a connection exists, reply with a message box and terminate the application. To test to see if your application is already running on a computer you can test that within vb as well.|||Thanks for the effort...
BUt can you tell me further how to pass the login in SP_WHO so I can get its sp_id and then execute the KILL?
i tried this but an error occured...
declare @.@.user_id varchar(20)
set @.@.user_id='TheUserIDLogged'
sp_who @.@.user_id
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@.@.user_id'.
Thanks in advance...
I want to restrict a user from logging in different workstations or creating a multi-sessions of my application. HOw can I remotely kill the application if he tries to login again considering that he has still live connection or open application in the same pc or another? Assuming the user has successfully logged on to the other pc, how can I send a message to his original opened application informing that connection is closed or something like.
If you are familiar with Yahoo Messenger, you will understand my point.
Im using VB6 and MSSQL 2000...
Anybody who has an answer for this please help. As administrator, we normally prevent users from opening different sessions.
Assuming I can kill a live connection using KILL (sp_id) in SQL Server, In VB app, how can I test the connection if its alive or not coz I might use a timer to check every second or a minute so a message box will appear saying that connection is killed remotely and the application terminates?
declare @.@.user_id varchar(20)
set @.@.user_id='TheUserIDLogged'
sp_who @.@.user_id
I also tried to do this but error occurs:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@.@.user_id'.
How can i get the sp_id of this connection so that I can execute the
KILL function?
Any relevant idea is highly appreciated...ThanksHow do they login?
What's your security model?
And if you find multiple logins, which one do you pick to kill (Batai?)
USE Northwind
GO
CREATE TABLE tbl_sp_Who2 (
SPID int
, Status varchar(255)
, Login varchar(255)
, HostName varchar(255)
, BlkBy varchar(255)
, DBName varchar(255)
, Command varchar(255)
, CPUTime int
, DiskIO int
, LastBatch varchar(255)
, ProgramName varchar(255)
, SPID2 int)
GO
INSERT INTO tbl_sp_Who2 EXEC sp_who2 active
SELECT *
FROM tbl_sp_Who2
WHERE Login IN ( SELECT Login
FROM tbl_sp_Who2
GROUP BY Login
HAVING COUNT(*) > 1)
GO
DROP TABLE tbl_sp_Who2
GO
HTH|||After opening the initial connection, I would test to see if this user already has open connections using the sp_who command passing the login. If a connection exists, reply with a message box and terminate the application. To test to see if your application is already running on a computer you can test that within vb as well.|||Thanks for the effort...
BUt can you tell me further how to pass the login in SP_WHO so I can get its sp_id and then execute the KILL?
i tried this but an error occured...
declare @.@.user_id varchar(20)
set @.@.user_id='TheUserIDLogged'
sp_who @.@.user_id
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@.@.user_id'.
Thanks in advance...
Monday, March 26, 2012
killing process
Hi everyone,
There was some process I wanted to kill with KILL command but I couldnt
because it was said that it was rolling back transaction and time left is 0
ms(?). LastBatch in sp_who2 showed time around two weeks ago. The process
seemed to be doing nothing.
Eventually I had to restart server (development environment, so no problems
with that), but are there any other way to get rid of such processes ?
Thanks a lot for any info
Alex
You would need to find out whatr the spid was doing.
Try dbcc inputbuffer and fn_getsql to see if they give you anything.
If it is calling an external app you might be able to just kill that
task on the server.
If it's just got stuck or calling an in process app then you would have
to bounce sql server or restart the machine.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||This is typical of an external process called by SQL Agent that has been
hung up on some external issue. Before you kill the spid, you need to tell
the SQL Agent job to cancel the activity. Then, if the spid remains, you
can kill it. Otherwise, the SQL Agent then gets hung on releasing hooks.
This is typical of a failed sqlmaint process hung on a failed MAPI client or
ActiveX process through xp_cmdshell.
Sincerely,
Anthony Thomas
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:O7$Tdfn2EHA.2012@.TK2MSFTNGP15.phx.gbl...
You would need to find out whatr the spid was doing.
Try dbcc inputbuffer and fn_getsql to see if they give you anything.
If it is calling an external app you might be able to just kill that
task on the server.
If it's just got stuck or calling an in process app then you would have
to bounce sql server or restart the machine.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
There was some process I wanted to kill with KILL command but I couldnt
because it was said that it was rolling back transaction and time left is 0
ms(?). LastBatch in sp_who2 showed time around two weeks ago. The process
seemed to be doing nothing.
Eventually I had to restart server (development environment, so no problems
with that), but are there any other way to get rid of such processes ?
Thanks a lot for any info
Alex
You would need to find out whatr the spid was doing.
Try dbcc inputbuffer and fn_getsql to see if they give you anything.
If it is calling an external app you might be able to just kill that
task on the server.
If it's just got stuck or calling an in process app then you would have
to bounce sql server or restart the machine.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||This is typical of an external process called by SQL Agent that has been
hung up on some external issue. Before you kill the spid, you need to tell
the SQL Agent job to cancel the activity. Then, if the spid remains, you
can kill it. Otherwise, the SQL Agent then gets hung on releasing hooks.
This is typical of a failed sqlmaint process hung on a failed MAPI client or
ActiveX process through xp_cmdshell.
Sincerely,
Anthony Thomas
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:O7$Tdfn2EHA.2012@.TK2MSFTNGP15.phx.gbl...
You would need to find out whatr the spid was doing.
Try dbcc inputbuffer and fn_getsql to see if they give you anything.
If it is calling an external app you might be able to just kill that
task on the server.
If it's just got stuck or calling an in process app then you would have
to bounce sql server or restart the machine.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
killing process
Hi everyone,
There was some process I wanted to kill with KILL command but I couldnt
because it was said that it was rolling back transaction and time left is 0
ms(?). LastBatch in sp_who2 showed time around two weeks ago. The process
seemed to be doing nothing.
Eventually I had to restart server (development environment, so no problems
with that), but are there any other way to get rid of such processes ?
Thanks a lot for any info
AlexYou would need to find out whatr the spid was doing.
Try dbcc inputbuffer and fn_getsql to see if they give you anything.
If it is calling an external app you might be able to just kill that
task on the server.
If it's just got stuck or calling an in process app then you would have
to bounce sql server or restart the machine.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||This is typical of an external process called by SQL Agent that has been
hung up on some external issue. Before you kill the spid, you need to tell
the SQL Agent job to cancel the activity. Then, if the spid remains, you
can kill it. Otherwise, the SQL Agent then gets hung on releasing hooks.
This is typical of a failed sqlmaint process hung on a failed MAPI client or
ActiveX process through xp_cmdshell.
Sincerely,
Anthony Thomas
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:O7$Tdfn2EHA.2012@.TK2MSFTNGP15.phx.gbl...
You would need to find out whatr the spid was doing.
Try dbcc inputbuffer and fn_getsql to see if they give you anything.
If it is calling an external app you might be able to just kill that
task on the server.
If it's just got stuck or calling an in process app then you would have
to bounce sql server or restart the machine.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
There was some process I wanted to kill with KILL command but I couldnt
because it was said that it was rolling back transaction and time left is 0
ms(?). LastBatch in sp_who2 showed time around two weeks ago. The process
seemed to be doing nothing.
Eventually I had to restart server (development environment, so no problems
with that), but are there any other way to get rid of such processes ?
Thanks a lot for any info
AlexYou would need to find out whatr the spid was doing.
Try dbcc inputbuffer and fn_getsql to see if they give you anything.
If it is calling an external app you might be able to just kill that
task on the server.
If it's just got stuck or calling an in process app then you would have
to bounce sql server or restart the machine.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||This is typical of an external process called by SQL Agent that has been
hung up on some external issue. Before you kill the spid, you need to tell
the SQL Agent job to cancel the activity. Then, if the spid remains, you
can kill it. Otherwise, the SQL Agent then gets hung on releasing hooks.
This is typical of a failed sqlmaint process hung on a failed MAPI client or
ActiveX process through xp_cmdshell.
Sincerely,
Anthony Thomas
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:O7$Tdfn2EHA.2012@.TK2MSFTNGP15.phx.gbl...
You would need to find out whatr the spid was doing.
Try dbcc inputbuffer and fn_getsql to see if they give you anything.
If it is calling an external app you might be able to just kill that
task on the server.
If it's just got stuck or calling an in process app then you would have
to bounce sql server or restart the machine.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Killing mupltiple batches
Is there a SQL command which will kill or exit all batches in the query
analyzer?
For example I have this script:
query1
query2
GO
query3
query4
query5
GO
Is there a way so that when I check @.@.ERROR after query1 that query2,3,4,5
do NOT get executed? GOTO's cannot see beyond the next GO.
Thanks,
Greg
There is no neat way to do this in Query Analyzer except raising an error
with severity 20 or higher, which will terminate your connection:
RAISERROR ('Your message here.', 20, 1)
If you use osql to run a script, you can raise an error with a status of
127, which will have the same effect, but won't leave traces in the SQL
Server error log like raising an error with status 20 does.
RAISERROR ('Your message here.', 16, 127)
Jacco Schalkwijk
SQL Server MVP
"Greg Michalopoulos" <gmichalopoulos@.d2hawkeye.com> wrote in message
news:OYM9dqG5EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Is there a SQL command which will kill or exit all batches in the query
> analyzer?
> For example I have this script:
> query1
> query2
> GO
> query3
> query4
> query5
> GO
> Is there a way so that when I check @.@.ERROR after query1 that query2,3,4,5
> do NOT get executed? GOTO's cannot see beyond the next GO.
> Thanks,
> Greg
>
|||Hello I had a similar problem as original poster - needing to kill multiple
batches within the same script.
Raiserror is not working for me.
raiserror ('just kill me now',19,1, 'WITH LOG,NOWAIT')
comes back with
Server: Msg 2754, Level 16, State 1, Line 1
Error severity levels greater than 18 can only be specified by members of
the sysadmin role, using the WITH LOG option.
I am sure that the account i am using has the sysadmin fixed server role.
PLEASE HELP!
Thanks,
Joel Mariano
"Jacco Schalkwijk" wrote:
> There is no neat way to do this in Query Analyzer except raising an error
> with severity 20 or higher, which will terminate your connection:
> RAISERROR ('Your message here.', 20, 1)
> If you use osql to run a script, you can raise an error with a status of
> 127, which will have the same effect, but won't leave traces in the SQL
> Server error log like raising an error with status 20 does.
> RAISERROR ('Your message here.', 16, 127)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Greg Michalopoulos" <gmichalopoulos@.d2hawkeye.com> wrote in message
> news:OYM9dqG5EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
>
|||Whoops, brain fart:
raiserror ('just kill me now',20,1) WITH LOG,NOWAIT
works just fine (drops the connection).
"Joel Mariano" wrote:
[vbcol=seagreen]
> Hello I had a similar problem as original poster - needing to kill multiple
> batches within the same script.
> Raiserror is not working for me.
> raiserror ('just kill me now',19,1, 'WITH LOG,NOWAIT')
> comes back with
> Server: Msg 2754, Level 16, State 1, Line 1
> Error severity levels greater than 18 can only be specified by members of
> the sysadmin role, using the WITH LOG option.
> I am sure that the account i am using has the sysadmin fixed server role.
> PLEASE HELP!
> Thanks,
> Joel Mariano
> "Jacco Schalkwijk" wrote:
sql
analyzer?
For example I have this script:
query1
query2
GO
query3
query4
query5
GO
Is there a way so that when I check @.@.ERROR after query1 that query2,3,4,5
do NOT get executed? GOTO's cannot see beyond the next GO.
Thanks,
Greg
There is no neat way to do this in Query Analyzer except raising an error
with severity 20 or higher, which will terminate your connection:
RAISERROR ('Your message here.', 20, 1)
If you use osql to run a script, you can raise an error with a status of
127, which will have the same effect, but won't leave traces in the SQL
Server error log like raising an error with status 20 does.
RAISERROR ('Your message here.', 16, 127)
Jacco Schalkwijk
SQL Server MVP
"Greg Michalopoulos" <gmichalopoulos@.d2hawkeye.com> wrote in message
news:OYM9dqG5EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Is there a SQL command which will kill or exit all batches in the query
> analyzer?
> For example I have this script:
> query1
> query2
> GO
> query3
> query4
> query5
> GO
> Is there a way so that when I check @.@.ERROR after query1 that query2,3,4,5
> do NOT get executed? GOTO's cannot see beyond the next GO.
> Thanks,
> Greg
>
|||Hello I had a similar problem as original poster - needing to kill multiple
batches within the same script.
Raiserror is not working for me.
raiserror ('just kill me now',19,1, 'WITH LOG,NOWAIT')
comes back with
Server: Msg 2754, Level 16, State 1, Line 1
Error severity levels greater than 18 can only be specified by members of
the sysadmin role, using the WITH LOG option.
I am sure that the account i am using has the sysadmin fixed server role.
PLEASE HELP!
Thanks,
Joel Mariano
"Jacco Schalkwijk" wrote:
> There is no neat way to do this in Query Analyzer except raising an error
> with severity 20 or higher, which will terminate your connection:
> RAISERROR ('Your message here.', 20, 1)
> If you use osql to run a script, you can raise an error with a status of
> 127, which will have the same effect, but won't leave traces in the SQL
> Server error log like raising an error with status 20 does.
> RAISERROR ('Your message here.', 16, 127)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Greg Michalopoulos" <gmichalopoulos@.d2hawkeye.com> wrote in message
> news:OYM9dqG5EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
>
|||Whoops, brain fart:
raiserror ('just kill me now',20,1) WITH LOG,NOWAIT
works just fine (drops the connection).
"Joel Mariano" wrote:
[vbcol=seagreen]
> Hello I had a similar problem as original poster - needing to kill multiple
> batches within the same script.
> Raiserror is not working for me.
> raiserror ('just kill me now',19,1, 'WITH LOG,NOWAIT')
> comes back with
> Server: Msg 2754, Level 16, State 1, Line 1
> Error severity levels greater than 18 can only be specified by members of
> the sysadmin role, using the WITH LOG option.
> I am sure that the account i am using has the sysadmin fixed server role.
> PLEASE HELP!
> Thanks,
> Joel Mariano
> "Jacco Schalkwijk" wrote:
sql
Killing an active connection
Is there any stored procedure to kill an active database connection?
Thanks
use sp_who to find out what sessions are running.
use kill <spid> to kill the session you want.
|||Sorry forgot to mention - if it is a long running transaction, you need
to be aware. As if you kill that, it may even take LONGER to finish
off and you cannot kill a rollback transaction.
|||Thanks so much!
"MSLam" <MelodySLam@.googlemail.com> escreveu na mensagem
news:1143580695.100118.311230@.e56g2000cwe.googlegr oups.com...
> Sorry forgot to mention - if it is a long running transaction, you need
> to be aware. As if you kill that, it may even take LONGER to finish
> off and you cannot kill a rollback transaction.
>
Thanks
use sp_who to find out what sessions are running.
use kill <spid> to kill the session you want.
|||Sorry forgot to mention - if it is a long running transaction, you need
to be aware. As if you kill that, it may even take LONGER to finish
off and you cannot kill a rollback transaction.
|||Thanks so much!
"MSLam" <MelodySLam@.googlemail.com> escreveu na mensagem
news:1143580695.100118.311230@.e56g2000cwe.googlegr oups.com...
> Sorry forgot to mention - if it is a long running transaction, you need
> to be aware. As if you kill that, it may even take LONGER to finish
> off and you cannot kill a rollback transaction.
>
Killing an active connection
Is there any stored procedure to kill an active database connection?
Thanksuse sp_who to find out what sessions are running.
use kill <spid> to kill the session you want.|||Sorry forgot to mention - if it is a long running transaction, you need
to be aware. As if you kill that, it may even take LONGER to finish
off and you cannot kill a rollback transaction.|||Thanks so much!
"MSLam" <MelodySLam@.googlemail.com> escreveu na mensagem
news:1143580695.100118.311230@.e56g2000cwe.googlegroups.com...
> Sorry forgot to mention - if it is a long running transaction, you need
> to be aware. As if you kill that, it may even take LONGER to finish
> off and you cannot kill a rollback transaction.
>sql
Thanksuse sp_who to find out what sessions are running.
use kill <spid> to kill the session you want.|||Sorry forgot to mention - if it is a long running transaction, you need
to be aware. As if you kill that, it may even take LONGER to finish
off and you cannot kill a rollback transaction.|||Thanks so much!
"MSLam" <MelodySLam@.googlemail.com> escreveu na mensagem
news:1143580695.100118.311230@.e56g2000cwe.googlegroups.com...
> Sorry forgot to mention - if it is a long running transaction, you need
> to be aware. As if you kill that, it may even take LONGER to finish
> off and you cannot kill a rollback transaction.
>sql
Killing an active connection
Is there any stored procedure to kill an active database connection?
Thanksuse sp_who to find out what sessions are running.
use kill <spid> to kill the session you want.|||Sorry forgot to mention - if it is a long running transaction, you need
to be aware. As if you kill that, it may even take LONGER to finish
off and you cannot kill a rollback transaction.|||Thanks so much!
"MSLam" <MelodySLam@.googlemail.com> escreveu na mensagem
news:1143580695.100118.311230@.e56g2000cwe.googlegroups.com...
> Sorry forgot to mention - if it is a long running transaction, you need
> to be aware. As if you kill that, it may even take LONGER to finish
> off and you cannot kill a rollback transaction.
>
Thanksuse sp_who to find out what sessions are running.
use kill <spid> to kill the session you want.|||Sorry forgot to mention - if it is a long running transaction, you need
to be aware. As if you kill that, it may even take LONGER to finish
off and you cannot kill a rollback transaction.|||Thanks so much!
"MSLam" <MelodySLam@.googlemail.com> escreveu na mensagem
news:1143580695.100118.311230@.e56g2000cwe.googlegroups.com...
> Sorry forgot to mention - if it is a long running transaction, you need
> to be aware. As if you kill that, it may even take LONGER to finish
> off and you cannot kill a rollback transaction.
>
Killing all sleeping processes
Hi All,
When I run sp_who2, I see there are many sleeping processes. Instead of
killing one by one, I was wondering if there is any way to kill all
sleeping processes programmatically (MS SQL Server 2000).
Thanks a million in advance.
Best regards,
mamunHi,
It is possible to do. But Killing the sleeping user process may not be a
good idea. A user processes
may be running for 40 minutes and when you check that process may be
sleeping and you code will kill that
process.
I recommend you to, not to automate this process in production server.
Script:-
use master
go
declare @.x varchar(1000)
set @.x=''
select @.x = @.x + 'Kill' + convert(varchar(5), spid)
from master.dbo.sysprocesses where status='Sleeping'
exec (@.x)
Schedule the script thru SQL Agent jobs. THe above script can take care of 1
user kill . If you have mutiple user, you have to slightly modify the script
Thanks
Hari
SQL Server MVP
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1116964671.573914.189310@.g49g2000cwa.googlegroups.com...
> Hi All,
>
> When I run sp_who2, I see there are many sleeping processes. Instead of
> killing one by one, I was wondering if there is any way to kill all
> sleeping processes programmatically (MS SQL Server 2000).
> Thanks a million in advance.
> Best regards,
> mamun
>|||If the unneeded idle connections are created by applications, then it's an
issue for the developer to resolve by managing the connection pool. If the
connections are created by people logging into Query Analyzer or Enterprise
Manager, then it's an issue for the DBA to resolve by perhaps restricting
logins and permissions.
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1116964671.573914.189310@.g49g2000cwa.googlegroups.com...
> Hi All,
>
> When I run sp_who2, I see there are many sleeping processes. Instead of
> killing one by one, I was wondering if there is any way to kill all
> sleeping processes programmatically (MS SQL Server 2000).
> Thanks a million in advance.
> Best regards,
> mamun
>
When I run sp_who2, I see there are many sleeping processes. Instead of
killing one by one, I was wondering if there is any way to kill all
sleeping processes programmatically (MS SQL Server 2000).
Thanks a million in advance.
Best regards,
mamunHi,
It is possible to do. But Killing the sleeping user process may not be a
good idea. A user processes
may be running for 40 minutes and when you check that process may be
sleeping and you code will kill that
process.
I recommend you to, not to automate this process in production server.
Script:-
use master
go
declare @.x varchar(1000)
set @.x=''
select @.x = @.x + 'Kill' + convert(varchar(5), spid)
from master.dbo.sysprocesses where status='Sleeping'
exec (@.x)
Schedule the script thru SQL Agent jobs. THe above script can take care of 1
user kill . If you have mutiple user, you have to slightly modify the script
Thanks
Hari
SQL Server MVP
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1116964671.573914.189310@.g49g2000cwa.googlegroups.com...
> Hi All,
>
> When I run sp_who2, I see there are many sleeping processes. Instead of
> killing one by one, I was wondering if there is any way to kill all
> sleeping processes programmatically (MS SQL Server 2000).
> Thanks a million in advance.
> Best regards,
> mamun
>|||If the unneeded idle connections are created by applications, then it's an
issue for the developer to resolve by managing the connection pool. If the
connections are created by people logging into Query Analyzer or Enterprise
Manager, then it's an issue for the DBA to resolve by perhaps restricting
logins and permissions.
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1116964671.573914.189310@.g49g2000cwa.googlegroups.com...
> Hi All,
>
> When I run sp_who2, I see there are many sleeping processes. Instead of
> killing one by one, I was wondering if there is any way to kill all
> sleeping processes programmatically (MS SQL Server 2000).
> Thanks a million in advance.
> Best regards,
> mamun
>
Killing active connections before detaching a database
How can I kill all active connections to a database before detaching it?
I am calling sp_detach_db from an application but I keep getting the error
"Cannot detach because there are one or more active connections."
Thanks,
RonKill spid
Madhivanan|||2 ways
1
--loop through the sysprocesses
DECLARE @.sysDbName SYSNAME
SELECT @.sysDbName = 'northwind'
SELECT IDENTITY(int, 1,1)AS ID,spid
INTO #LoopProcess
FROM master..sysprocesses
WHERE dbid = DB_ID(@.sysDbName)
DECLARE @.SPID SMALLINT
DECLARE @.SQL VARCHAR(255)
DECLARE @.MaxID INT, @.LoopID INT
SELECT @.LoopID =1,@.MaxID = MAX(ID) FROM #LoopProcess
WHILE @.LoopID <= @.MaxID
BEGIN
SELECT @.SPID = spid FROM #LoopProcess WHERE ID = @.LoopID
SELECT @.SQL = 'KILL ' + CONVERT(VARCHAR, @.SPID)
EXEC( @.SQL )
SELECT @.LoopID = @.LoopID +1
END
DROP TABLE #LoopProcess
2
--alter the DB by making it single user (all transaction will be rolled
back)
ALTER DATABASE northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--do your restore here
-- Make the DB multi user again
ALTER DATABASE northwind SET MULTI_USER
http://sqlservercode.blogspot.com/|||That won't stop them from "crawling" back. ;)
Use:
use master
alter database <database name>
set single_user
with rollback immediate
...to kill all users immediately, or:
alter database <database name>
set single_user
with rollback after <number> seconds
...to give them time to finish their work.
ML
http://milambda.blogspot.com/|||That's what I call the "nuclear option". ;-)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:15E4ACA2-392A-4986-B4A5-7E52EA9DDB33@.microsoft.com...
> That won't stop them from "crawling" back. ;)
> Use:
> use master
> alter database <database name>
> set single_user
> with rollback immediate
> ...to kill all users immediately, or:
> alter database <database name>
> set single_user
> with rollback after <number> seconds
> ...to give them time to finish their work.
>
> ML
> --
> http://milambda.blogspot.com/|||THANKS!
No problem, the databases that will be called by this procedure are only
used under controlled circustances.
Ron
"JT" <someone@.microsoft.com> wrote in message
news:OkmBFzUOGHA.3100@.TK2MSFTNGP11.phx.gbl...
> That's what I call the "nuclear option". ;-)
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:15E4ACA2-392A-4986-B4A5-7E52EA9DDB33@.microsoft.com...
>|||When you gotta nuke'em, you gotta nuke'em. :)
ML
http://milambda.blogspot.com/
I am calling sp_detach_db from an application but I keep getting the error
"Cannot detach because there are one or more active connections."
Thanks,
RonKill spid
Madhivanan|||2 ways
1
--loop through the sysprocesses
DECLARE @.sysDbName SYSNAME
SELECT @.sysDbName = 'northwind'
SELECT IDENTITY(int, 1,1)AS ID,spid
INTO #LoopProcess
FROM master..sysprocesses
WHERE dbid = DB_ID(@.sysDbName)
DECLARE @.SPID SMALLINT
DECLARE @.SQL VARCHAR(255)
DECLARE @.MaxID INT, @.LoopID INT
SELECT @.LoopID =1,@.MaxID = MAX(ID) FROM #LoopProcess
WHILE @.LoopID <= @.MaxID
BEGIN
SELECT @.SPID = spid FROM #LoopProcess WHERE ID = @.LoopID
SELECT @.SQL = 'KILL ' + CONVERT(VARCHAR, @.SPID)
EXEC( @.SQL )
SELECT @.LoopID = @.LoopID +1
END
DROP TABLE #LoopProcess
2
--alter the DB by making it single user (all transaction will be rolled
back)
ALTER DATABASE northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--do your restore here
-- Make the DB multi user again
ALTER DATABASE northwind SET MULTI_USER
http://sqlservercode.blogspot.com/|||That won't stop them from "crawling" back. ;)
Use:
use master
alter database <database name>
set single_user
with rollback immediate
...to kill all users immediately, or:
alter database <database name>
set single_user
with rollback after <number> seconds
...to give them time to finish their work.
ML
http://milambda.blogspot.com/|||That's what I call the "nuclear option". ;-)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:15E4ACA2-392A-4986-B4A5-7E52EA9DDB33@.microsoft.com...
> That won't stop them from "crawling" back. ;)
> Use:
> use master
> alter database <database name>
> set single_user
> with rollback immediate
> ...to kill all users immediately, or:
> alter database <database name>
> set single_user
> with rollback after <number> seconds
> ...to give them time to finish their work.
>
> ML
> --
> http://milambda.blogspot.com/|||THANKS!
No problem, the databases that will be called by this procedure are only
used under controlled circustances.
Ron
"JT" <someone@.microsoft.com> wrote in message
news:OkmBFzUOGHA.3100@.TK2MSFTNGP11.phx.gbl...
> That's what I call the "nuclear option". ;-)
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:15E4ACA2-392A-4986-B4A5-7E52EA9DDB33@.microsoft.com...
>|||When you gotta nuke'em, you gotta nuke'em. :)
ML
http://milambda.blogspot.com/
killing a user process
Hi,
I have a user process related to sql mail (xp_readmail) which was submitted.
It was taking a longtime to execute, hence I tried to kill this process using
KILL command. The status it is showing me now is KILLED\ROLLBACK. But when I
again execute the KILL command WITH_STATUS_ONLY, it says rollback is
completed successfully. But still the process is not killed and it stays in
this mode for indefinite time.
How can I kill such kind of process? I even tried re-starting
SQLServeragent, but it didn't help. Anybody is facing such problems?
Thanks
GYKIt's running mapi and you may need to use the command line
kill to kill the mapi32 OS process - or use task manager and
kill the processes.
-Sue
On Tue, 28 Sep 2004 12:15:03 -0700, GYK
<GYK@.discussions.microsoft.com> wrote:
>Hi,
>I have a user process related to sql mail (xp_readmail) which was submitted.
>It was taking a longtime to execute, hence I tried to kill this process using
>KILL command. The status it is showing me now is KILLED\ROLLBACK. But when I
>again execute the KILL command WITH_STATUS_ONLY, it says rollback is
>completed successfully. But still the process is not killed and it stays in
>this mode for indefinite time.
>How can I kill such kind of process? I even tried re-starting
>SQLServeragent, but it didn't help. Anybody is facing such problems?
>Thanks
>GYK
I have a user process related to sql mail (xp_readmail) which was submitted.
It was taking a longtime to execute, hence I tried to kill this process using
KILL command. The status it is showing me now is KILLED\ROLLBACK. But when I
again execute the KILL command WITH_STATUS_ONLY, it says rollback is
completed successfully. But still the process is not killed and it stays in
this mode for indefinite time.
How can I kill such kind of process? I even tried re-starting
SQLServeragent, but it didn't help. Anybody is facing such problems?
Thanks
GYKIt's running mapi and you may need to use the command line
kill to kill the mapi32 OS process - or use task manager and
kill the processes.
-Sue
On Tue, 28 Sep 2004 12:15:03 -0700, GYK
<GYK@.discussions.microsoft.com> wrote:
>Hi,
>I have a user process related to sql mail (xp_readmail) which was submitted.
>It was taking a longtime to execute, hence I tried to kill this process using
>KILL command. The status it is showing me now is KILLED\ROLLBACK. But when I
>again execute the KILL command WITH_STATUS_ONLY, it says rollback is
>completed successfully. But still the process is not killed and it stays in
>this mode for indefinite time.
>How can I kill such kind of process? I even tried re-starting
>SQLServeragent, but it didn't help. Anybody is facing such problems?
>Thanks
>GYK
killing a user process
Hi,
I have a user process related to sql mail (xp_readmail) which was submitted.
It was taking a longtime to execute, hence I tried to kill this process using
KILL command. The status it is showing me now is KILLED\ROLLBACK. But when I
again execute the KILL command WITH_STATUS_ONLY, it says rollback is
completed successfully. But still the process is not killed and it stays in
this mode for indefinite time.
How can I kill such kind of process? I even tried re-starting
SQLServeragent, but it didn't help. Anybody is facing such problems?
Thanks
GYK
It's running mapi and you may need to use the command line
kill to kill the mapi32 OS process - or use task manager and
kill the processes.
-Sue
On Tue, 28 Sep 2004 12:15:03 -0700, GYK
<GYK@.discussions.microsoft.com> wrote:
>Hi,
>I have a user process related to sql mail (xp_readmail) which was submitted.
>It was taking a longtime to execute, hence I tried to kill this process using
>KILL command. The status it is showing me now is KILLED\ROLLBACK. But when I
>again execute the KILL command WITH_STATUS_ONLY, it says rollback is
>completed successfully. But still the process is not killed and it stays in
>this mode for indefinite time.
>How can I kill such kind of process? I even tried re-starting
>SQLServeragent, but it didn't help. Anybody is facing such problems?
>Thanks
>GYK
I have a user process related to sql mail (xp_readmail) which was submitted.
It was taking a longtime to execute, hence I tried to kill this process using
KILL command. The status it is showing me now is KILLED\ROLLBACK. But when I
again execute the KILL command WITH_STATUS_ONLY, it says rollback is
completed successfully. But still the process is not killed and it stays in
this mode for indefinite time.
How can I kill such kind of process? I even tried re-starting
SQLServeragent, but it didn't help. Anybody is facing such problems?
Thanks
GYK
It's running mapi and you may need to use the command line
kill to kill the mapi32 OS process - or use task manager and
kill the processes.
-Sue
On Tue, 28 Sep 2004 12:15:03 -0700, GYK
<GYK@.discussions.microsoft.com> wrote:
>Hi,
>I have a user process related to sql mail (xp_readmail) which was submitted.
>It was taking a longtime to execute, hence I tried to kill this process using
>KILL command. The status it is showing me now is KILLED\ROLLBACK. But when I
>again execute the KILL command WITH_STATUS_ONLY, it says rollback is
>completed successfully. But still the process is not killed and it stays in
>this mode for indefinite time.
>How can I kill such kind of process? I even tried re-starting
>SQLServeragent, but it didn't help. Anybody is facing such problems?
>Thanks
>GYK
Subscribe to:
Posts (Atom)