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 killing. Show all posts
Showing posts with label killing. 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 process automatically
Hi everybody,
We have a very large database and high transaction volume. Time to time
these transactions are locking each other and decrease the performance
of the database. Is there any way that I can automate the killing
process when blocking and deadlock time is exceeded in certain time
elipsade? Can somebody help me on this please?
Regards
asa.laststubborn wrote:
> Hi everybody,
> We have a very large database and high transaction volume. Time to time
> these transactions are locking each other and decrease the performance
> of the database. Is there any way that I can automate the killing
> process when blocking and deadlock time is exceeded in certain time
> elipsade? Can somebody help me on this please?
If SQL Server detects a deadlock it will kill one of the two involved TX
automatically. But you should really change your app to prevent these
deadlocks.
You probably cannot do much about normal locking as this is expected
behavior other than probably optimizing your SQL to make it faster.
HTH
robert|||Is it possible to change this deadlock killing time? for instance lets
say instead of 5 min change it to 2 min??
Thanks|||laststubborn wrote:
> Is it possible to change this deadlock killing time? for instance lets
> say instead of 5 min change it to 2 min??
read the docs (BOL)
Customizing the Lock Time-out
When Microsoft SQL Server 2000 cannot grant a lock to a transaction on
a resource because another transaction already owns a conflicting lock
on that resource, the first transaction becomes blocked waiting on that
resource. If this causes a deadlock, SQL Server terminates one of the
participating transactions (with no time-out involved). If there is no
deadlock, the transaction requesting the lock is blocked until the other
transaction releases the lock. By default, there is no mandatory
time-out period, and no way to test if a resource is locked before
locking it, except to attempt to access the data (and potentially get
blocked indefinitely).
robert|||laststubborn (arafatsalih@.gmail.com) writes:
> Is it possible to change this deadlock killing time? for instance lets
> say instead of 5 min change it to 2 min??
A deadlock does not take five minutes to sort out. It seems that you
have a misconception of what a deadlock is. A deadlock is when two
processes are blocking each other, so none of them can continue. This
is something that SQL Server detects automatically. It usually takes a
couple of seconds.
But one long-running process can block other processes (than in their
turn can block other processes etc) without any deadlock to occur.
I would advice against any automatic killing, as supposedly some processes
are more important than others. It's better to analyse what those blockers
are up to, and if the queries can be improved, or indexes added to
speed up these queries.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Not sure if this could be relevant but perhaps add WITH(NOLOCK) on your
queries.. With this, no locks would actually happen.|||"D0MZE" <domze.sa@.gmail.com> wrote in message
news:1144891406.165135.214850@.i40g2000cwc.googlegr oups.com...
> Not sure if this could be relevant but perhaps add WITH(NOLOCK) on your
> queries.. With this, no locks would actually happen.
Not quite.
For a select it basically means to ignore locks on rows.
This can mean you can get phantom rows, not get rows you should etc. i.e.
you'll get an inconsistent view of the table at the time.
This MAY be acceptable in some circumstances, but in others would be
completely verbotin. (imagine an ATM that did a look up on cache available
with a (NOLOCK) while your bank is deleting your last check. You'd falsely
be told you have more money available than you actually do and could
overdraw the account.)
killing 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 SQLmail
Our SQLMail stopped working, so I tried to stop and
restart the SQLMail using xp_stopmail and xp_startmail.
it stopped quickly, but when I ran xp_startmail, it
appeared to hang. I killed the process, now it's saying
that it's "Killed/Rollback". It's been like that for a
while now. Does anyone know a way to get rid of this?
Can this cause other headaches?
Rob,
Are you using Microsoft Exchange? Is the Exchange Server available? This
can happen when Exchange disappears, or you lose connectivity to it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Rob wrote:
> Our SQLMail stopped working, so I tried to stop and
> restart the SQLMail using xp_stopmail and xp_startmail.
> it stopped quickly, but when I ran xp_startmail, it
> appeared to hang. I killed the process, now it's saying
> that it's "Killed/Rollback". It's been like that for a
> while now. Does anyone know a way to get rid of this?
> Can this cause other headaches?
|||> This can happen when Exchange disappears
I'd question David Copperfield. ;-)
Oh, I can't wait for a more robust and independent mail platform in SQL
Server...
http://www.aspfaq.com/
(Reverse address to reply.)
|||I already tried that - that was also my first thought that is was because is
was doing it under wrong uder name, but that's not the case. I', logged in
to the server as the user that runs the SQLServer Agent service, and I can
open Outlook and get into the mailbox without any problems.
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:OtVlLiCZEHA.2500@.TK2MSFTNGP09.phx.gbl...
> I'd question David Copperfield. ;-)
> Oh, I can't wait for a more robust and independent mail platform in SQL
> Server...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||Steen,
Can you send mail from Outlook, logged on as the SQLAgent account?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steen Persson wrote:
> I already tried that - that was also my first thought that is was because is
> was doing it under wrong uder name, but that's not the case. I', logged in
> to the server as the user that runs the SQLServer Agent service, and I can
> open Outlook and get into the mailbox without any problems.
> Regards
> Steen
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
> news:OtVlLiCZEHA.2500@.TK2MSFTNGP09.phx.gbl...
>
>
|||...it seems like my previous reply to my problem has ended up in a wrong
thread - sorry 'bout that.
I have verified that I can send emails from Outlook. Everything seems to be
fine - I log on as the user that runs the SQL agent which is the same as the
user of the mailbox. The strange thing is that it worked fine the other day
when I installed Outlook on a few SQL servers, but now it fails with the
same error message on all of them. I'm wondering if there's something stupid
that I'm missing when I fire the xp_sendmail command.
The command I'm trying with just for the test is:
Use Master
EXEC xp_sendmail 'spe@.MyDomainName.dk', ' Test'
Shoudn't this be enough just to send an email or am I missing something?
Regards
Steen
"Mark Allison" <marka@.no.tinned.meat.mvps.org> skrev i en meddelelse
news:eFy9YYDZEHA.996@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Steen,
> Can you send mail from Outlook, logged on as the SQLAgent account?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Steen Persson wrote:
because is[vbcol=seagreen]
in[vbcol=seagreen]
can[vbcol=seagreen]
restart the SQLMail using xp_stopmail and xp_startmail.
it stopped quickly, but when I ran xp_startmail, it
appeared to hang. I killed the process, now it's saying
that it's "Killed/Rollback". It's been like that for a
while now. Does anyone know a way to get rid of this?
Can this cause other headaches?
Rob,
Are you using Microsoft Exchange? Is the Exchange Server available? This
can happen when Exchange disappears, or you lose connectivity to it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Rob wrote:
> Our SQLMail stopped working, so I tried to stop and
> restart the SQLMail using xp_stopmail and xp_startmail.
> it stopped quickly, but when I ran xp_startmail, it
> appeared to hang. I killed the process, now it's saying
> that it's "Killed/Rollback". It's been like that for a
> while now. Does anyone know a way to get rid of this?
> Can this cause other headaches?
|||> This can happen when Exchange disappears
I'd question David Copperfield. ;-)
Oh, I can't wait for a more robust and independent mail platform in SQL
Server...
http://www.aspfaq.com/
(Reverse address to reply.)
|||I already tried that - that was also my first thought that is was because is
was doing it under wrong uder name, but that's not the case. I', logged in
to the server as the user that runs the SQLServer Agent service, and I can
open Outlook and get into the mailbox without any problems.
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:OtVlLiCZEHA.2500@.TK2MSFTNGP09.phx.gbl...
> I'd question David Copperfield. ;-)
> Oh, I can't wait for a more robust and independent mail platform in SQL
> Server...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||Steen,
Can you send mail from Outlook, logged on as the SQLAgent account?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steen Persson wrote:
> I already tried that - that was also my first thought that is was because is
> was doing it under wrong uder name, but that's not the case. I', logged in
> to the server as the user that runs the SQLServer Agent service, and I can
> open Outlook and get into the mailbox without any problems.
> Regards
> Steen
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
> news:OtVlLiCZEHA.2500@.TK2MSFTNGP09.phx.gbl...
>
>
|||...it seems like my previous reply to my problem has ended up in a wrong
thread - sorry 'bout that.
I have verified that I can send emails from Outlook. Everything seems to be
fine - I log on as the user that runs the SQL agent which is the same as the
user of the mailbox. The strange thing is that it worked fine the other day
when I installed Outlook on a few SQL servers, but now it fails with the
same error message on all of them. I'm wondering if there's something stupid
that I'm missing when I fire the xp_sendmail command.
The command I'm trying with just for the test is:
Use Master
EXEC xp_sendmail 'spe@.MyDomainName.dk', ' Test'
Shoudn't this be enough just to send an email or am I missing something?
Regards
Steen
"Mark Allison" <marka@.no.tinned.meat.mvps.org> skrev i en meddelelse
news:eFy9YYDZEHA.996@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Steen,
> Can you send mail from Outlook, logged on as the SQLAgent account?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Steen Persson wrote:
because is[vbcol=seagreen]
in[vbcol=seagreen]
can[vbcol=seagreen]
killing SQLmail
Our SQLMail stopped working, so I tried to stop and
restart the SQLMail using xp_stopmail and xp_startmail.
it stopped quickly, but when I ran xp_startmail, it
appeared to hang. I killed the process, now it's saying
that it's "Killed/Rollback". It's been like that for a
while now. Does anyone know a way to get rid of this?
Can this cause other headaches?Rob,
Are you using Microsoft Exchange? Is the Exchange Server available? This
can happen when Exchange disappears, or you lose connectivity to it.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Rob wrote:
> Our SQLMail stopped working, so I tried to stop and
> restart the SQLMail using xp_stopmail and xp_startmail.
> it stopped quickly, but when I ran xp_startmail, it
> appeared to hang. I killed the process, now it's saying
> that it's "Killed/Rollback". It's been like that for a
> while now. Does anyone know a way to get rid of this?
> Can this cause other headaches?|||> This can happen when Exchange disappears
I'd question David Copperfield. ;-)
Oh, I can't wait for a more robust and independent mail platform in SQL
Server...
http://www.aspfaq.com/
(Reverse address to reply.)|||I already tried that - that was also my first thought that is was because is
was doing it under wrong uder name, but that's not the case. I', logged in
to the server as the user that runs the SQLServer Agent service, and I can
open Outlook and get into the mailbox without any problems.
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:OtVlLiCZEHA.2500@.TK2MSFTNGP09.phx.gbl...
> I'd question David Copperfield. ;-)
> Oh, I can't wait for a more robust and independent mail platform in SQL
> Server...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Steen,
Can you send mail from Outlook, logged on as the SQLAgent account?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steen Persson wrote:
> I already tried that - that was also my first thought that is was because
is
> was doing it under wrong uder name, but that's not the case. I', logged in
> to the server as the user that runs the SQLServer Agent service, and I can
> open Outlook and get into the mailbox without any problems.
> Regards
> Steen
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelels
e
> news:OtVlLiCZEHA.2500@.TK2MSFTNGP09.phx.gbl...
>
>
>|||...it seems like my previous reply to my problem has ended up in a wrong
thread - sorry 'bout that.
I have verified that I can send emails from Outlook. Everything seems to be
fine - I log on as the user that runs the SQL agent which is the same as the
user of the mailbox. The strange thing is that it worked fine the other day
when I installed Outlook on a few SQL servers, but now it fails with the
same error message on all of them. I'm wondering if there's something stupid
that I'm missing when I fire the xp_sendmail command.
The command I'm trying with just for the test is:
Use Master
EXEC xp_sendmail 'spe@.MyDomainName.dk', ' Test'
Shoudn't this be enough just to send an email or am I missing something?
Regards
Steen
"Mark Allison" <marka@.no.tinned.meat.mvps.org> skrev i en meddelelse
news:eFy9YYDZEHA.996@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Steen,
> Can you send mail from Outlook, logged on as the SQLAgent account?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Steen Persson wrote:
because is[vbcol=seagreen]
in[vbcol=seagreen]
can[vbcol=seagreen]sql
restart the SQLMail using xp_stopmail and xp_startmail.
it stopped quickly, but when I ran xp_startmail, it
appeared to hang. I killed the process, now it's saying
that it's "Killed/Rollback". It's been like that for a
while now. Does anyone know a way to get rid of this?
Can this cause other headaches?Rob,
Are you using Microsoft Exchange? Is the Exchange Server available? This
can happen when Exchange disappears, or you lose connectivity to it.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Rob wrote:
> Our SQLMail stopped working, so I tried to stop and
> restart the SQLMail using xp_stopmail and xp_startmail.
> it stopped quickly, but when I ran xp_startmail, it
> appeared to hang. I killed the process, now it's saying
> that it's "Killed/Rollback". It's been like that for a
> while now. Does anyone know a way to get rid of this?
> Can this cause other headaches?|||> This can happen when Exchange disappears
I'd question David Copperfield. ;-)
Oh, I can't wait for a more robust and independent mail platform in SQL
Server...
http://www.aspfaq.com/
(Reverse address to reply.)|||I already tried that - that was also my first thought that is was because is
was doing it under wrong uder name, but that's not the case. I', logged in
to the server as the user that runs the SQLServer Agent service, and I can
open Outlook and get into the mailbox without any problems.
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:OtVlLiCZEHA.2500@.TK2MSFTNGP09.phx.gbl...
> I'd question David Copperfield. ;-)
> Oh, I can't wait for a more robust and independent mail platform in SQL
> Server...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Steen,
Can you send mail from Outlook, logged on as the SQLAgent account?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steen Persson wrote:
> I already tried that - that was also my first thought that is was because
is
> was doing it under wrong uder name, but that's not the case. I', logged in
> to the server as the user that runs the SQLServer Agent service, and I can
> open Outlook and get into the mailbox without any problems.
> Regards
> Steen
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelels
e
> news:OtVlLiCZEHA.2500@.TK2MSFTNGP09.phx.gbl...
>
>
>|||...it seems like my previous reply to my problem has ended up in a wrong
thread - sorry 'bout that.
I have verified that I can send emails from Outlook. Everything seems to be
fine - I log on as the user that runs the SQL agent which is the same as the
user of the mailbox. The strange thing is that it worked fine the other day
when I installed Outlook on a few SQL servers, but now it fails with the
same error message on all of them. I'm wondering if there's something stupid
that I'm missing when I fire the xp_sendmail command.
The command I'm trying with just for the test is:
Use Master
EXEC xp_sendmail 'spe@.MyDomainName.dk', ' Test'
Shoudn't this be enough just to send an email or am I missing something?
Regards
Steen
"Mark Allison" <marka@.no.tinned.meat.mvps.org> skrev i en meddelelse
news:eFy9YYDZEHA.996@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Steen,
> Can you send mail from Outlook, logged on as the SQLAgent account?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Steen Persson wrote:
because is[vbcol=seagreen]
in[vbcol=seagreen]
can[vbcol=seagreen]sql
killing SQLmail
Our SQLMail stopped working, so I tried to stop and
restart the SQLMail using xp_stopmail and xp_startmail.
it stopped quickly, but when I ran xp_startmail, it
appeared to hang. I killed the process, now it's saying
that it's "Killed/Rollback". It's been like that for a
while now. Does anyone know a way to get rid of this?
Can this cause other headaches?Rob,
Are you using Microsoft Exchange? Is the Exchange Server available? This
can happen when Exchange disappears, or you lose connectivity to it.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Rob wrote:
> Our SQLMail stopped working, so I tried to stop and
> restart the SQLMail using xp_stopmail and xp_startmail.
> it stopped quickly, but when I ran xp_startmail, it
> appeared to hang. I killed the process, now it's saying
> that it's "Killed/Rollback". It's been like that for a
> while now. Does anyone know a way to get rid of this?
> Can this cause other headaches?|||> This can happen when Exchange disappears
I'd question David Copperfield. ;-)
Oh, I can't wait for a more robust and independent mail platform in SQL
Server...
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I already tried that - that was also my first thought that is was because is
was doing it under wrong uder name, but that's not the case. I', logged in
to the server as the user that runs the SQLServer Agent service, and I can
open Outlook and get into the mailbox without any problems.
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:OtVlLiCZEHA.2500@.TK2MSFTNGP09.phx.gbl...
> > This can happen when Exchange disappears
> I'd question David Copperfield. ;-)
> Oh, I can't wait for a more robust and independent mail platform in SQL
> Server...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Steen,
Can you send mail from Outlook, logged on as the SQLAgent account?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steen Persson wrote:
> I already tried that - that was also my first thought that is was because is
> was doing it under wrong uder name, but that's not the case. I', logged in
> to the server as the user that runs the SQLServer Agent service, and I can
> open Outlook and get into the mailbox without any problems.
> Regards
> Steen
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
> news:OtVlLiCZEHA.2500@.TK2MSFTNGP09.phx.gbl...
>>This can happen when Exchange disappears
>>I'd question David Copperfield. ;-)
>>Oh, I can't wait for a more robust and independent mail platform in SQL
>>Server...
>>--
>>http://www.aspfaq.com/
>>(Reverse address to reply.)
>>
>
>|||...it seems like my previous reply to my problem has ended up in a wrong
thread - sorry 'bout that.
I have verified that I can send emails from Outlook. Everything seems to be
fine - I log on as the user that runs the SQL agent which is the same as the
user of the mailbox. The strange thing is that it worked fine the other day
when I installed Outlook on a few SQL servers, but now it fails with the
same error message on all of them. I'm wondering if there's something stupid
that I'm missing when I fire the xp_sendmail command.
The command I'm trying with just for the test is:
Use Master
EXEC xp_sendmail 'spe@.MyDomainName.dk', ' Test'
Shoudn't this be enough just to send an email or am I missing something?
Regards
Steen
"Mark Allison" <marka@.no.tinned.meat.mvps.org> skrev i en meddelelse
news:eFy9YYDZEHA.996@.TK2MSFTNGP12.phx.gbl...
> Steen,
> Can you send mail from Outlook, logged on as the SQLAgent account?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Steen Persson wrote:
> > I already tried that - that was also my first thought that is was
because is
> > was doing it under wrong uder name, but that's not the case. I', logged
in
> > to the server as the user that runs the SQLServer Agent service, and I
can
> > open Outlook and get into the mailbox without any problems.
> >
> > Regards
> > Steen
> >
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
> > news:OtVlLiCZEHA.2500@.TK2MSFTNGP09.phx.gbl...
> >
> >>This can happen when Exchange disappears
> >>
> >>I'd question David Copperfield. ;-)
> >>
> >>Oh, I can't wait for a more robust and independent mail platform in SQL
> >>Server...
> >>
> >>--
> >>http://www.aspfaq.com/
> >>(Reverse address to reply.)
> >>
> >>
> >
> >
> >
restart the SQLMail using xp_stopmail and xp_startmail.
it stopped quickly, but when I ran xp_startmail, it
appeared to hang. I killed the process, now it's saying
that it's "Killed/Rollback". It's been like that for a
while now. Does anyone know a way to get rid of this?
Can this cause other headaches?Rob,
Are you using Microsoft Exchange? Is the Exchange Server available? This
can happen when Exchange disappears, or you lose connectivity to it.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Rob wrote:
> Our SQLMail stopped working, so I tried to stop and
> restart the SQLMail using xp_stopmail and xp_startmail.
> it stopped quickly, but when I ran xp_startmail, it
> appeared to hang. I killed the process, now it's saying
> that it's "Killed/Rollback". It's been like that for a
> while now. Does anyone know a way to get rid of this?
> Can this cause other headaches?|||> This can happen when Exchange disappears
I'd question David Copperfield. ;-)
Oh, I can't wait for a more robust and independent mail platform in SQL
Server...
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I already tried that - that was also my first thought that is was because is
was doing it under wrong uder name, but that's not the case. I', logged in
to the server as the user that runs the SQLServer Agent service, and I can
open Outlook and get into the mailbox without any problems.
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:OtVlLiCZEHA.2500@.TK2MSFTNGP09.phx.gbl...
> > This can happen when Exchange disappears
> I'd question David Copperfield. ;-)
> Oh, I can't wait for a more robust and independent mail platform in SQL
> Server...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Steen,
Can you send mail from Outlook, logged on as the SQLAgent account?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steen Persson wrote:
> I already tried that - that was also my first thought that is was because is
> was doing it under wrong uder name, but that's not the case. I', logged in
> to the server as the user that runs the SQLServer Agent service, and I can
> open Outlook and get into the mailbox without any problems.
> Regards
> Steen
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
> news:OtVlLiCZEHA.2500@.TK2MSFTNGP09.phx.gbl...
>>This can happen when Exchange disappears
>>I'd question David Copperfield. ;-)
>>Oh, I can't wait for a more robust and independent mail platform in SQL
>>Server...
>>--
>>http://www.aspfaq.com/
>>(Reverse address to reply.)
>>
>
>|||...it seems like my previous reply to my problem has ended up in a wrong
thread - sorry 'bout that.
I have verified that I can send emails from Outlook. Everything seems to be
fine - I log on as the user that runs the SQL agent which is the same as the
user of the mailbox. The strange thing is that it worked fine the other day
when I installed Outlook on a few SQL servers, but now it fails with the
same error message on all of them. I'm wondering if there's something stupid
that I'm missing when I fire the xp_sendmail command.
The command I'm trying with just for the test is:
Use Master
EXEC xp_sendmail 'spe@.MyDomainName.dk', ' Test'
Shoudn't this be enough just to send an email or am I missing something?
Regards
Steen
"Mark Allison" <marka@.no.tinned.meat.mvps.org> skrev i en meddelelse
news:eFy9YYDZEHA.996@.TK2MSFTNGP12.phx.gbl...
> Steen,
> Can you send mail from Outlook, logged on as the SQLAgent account?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Steen Persson wrote:
> > I already tried that - that was also my first thought that is was
because is
> > was doing it under wrong uder name, but that's not the case. I', logged
in
> > to the server as the user that runs the SQLServer Agent service, and I
can
> > open Outlook and get into the mailbox without any problems.
> >
> > Regards
> > Steen
> >
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
> > news:OtVlLiCZEHA.2500@.TK2MSFTNGP09.phx.gbl...
> >
> >>This can happen when Exchange disappears
> >>
> >>I'd question David Copperfield. ;-)
> >>
> >>Oh, I can't wait for a more robust and independent mail platform in SQL
> >>Server...
> >>
> >>--
> >>http://www.aspfaq.com/
> >>(Reverse address to reply.)
> >>
> >>
> >
> >
> >
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 Locks by Object - SS2005
splocI can't restore a database due to a locking issue. While I've killed
the Process in Activity Monitor, I still see the database listed on the Locks
By Object page. The Process ID is a negative number. Any ideas as to what I
can do to get rid of this lock?
Here's the output from sp_lock: -2 7 0 0 DB
S GRANT
Thanks in advance.
JohnOn Oct 5, 1:49 am, John Roberts
<JohnRobe...@.discussions.microsoft.com> wrote:
> splocI can't restore a database due to a locking issue. While I've killed
> the Process in Activity Monitor, I still see the database listed on the Locks
> By Object page. The Process ID is a negative number. Any ideas as to what I
> can do to get rid of this lock?
> Here's the output from sp_lock: -2 7 0 0 DB
> S GRANT
> Thanks in advance.
> John
After killing the process you may try putting database in single user
mode which would prevent application or user establishing connection.
Thanks
VS|||Thanks for the response..
When I do a select distinct req_transactionuow, req_transactionID from
syslockinfo where req_spid = -2
I see the follwing:
req_transactionuow req_transactionID
--
--
00000000-0000-0000-0000-000000000000 0
When I try to kill this UOW using the guid of all zeroes, we get the
following error:
Msg 6110, Level 16, State 1, Line 1
The distributed transaction with UOW {00000000-0000-0000-0000-000000000000}
does not exist.
Anybody out there familiar with killing orphaned transactions where the UOW
GUID is all zeros? My only solution now is to restart the service and, as
you might have imagined, that's NOT the only database running!!
Thanks in advance.
John
"vijay" wrote:
> On Oct 5, 1:49 am, John Roberts
> <JohnRobe...@.discussions.microsoft.com> wrote:
> > splocI can't restore a database due to a locking issue. While I've killed
> > the Process in Activity Monitor, I still see the database listed on the Locks
> > By Object page. The Process ID is a negative number. Any ideas as to what I
> > can do to get rid of this lock?
> >
> > Here's the output from sp_lock: -2 7 0 0 DB
> > S GRANT
> >
> > Thanks in advance.
> >
> > John
> After killing the process you may try putting database in single user
> mode which would prevent application or user establishing connection.
> Thanks
> VS
>
the Process in Activity Monitor, I still see the database listed on the Locks
By Object page. The Process ID is a negative number. Any ideas as to what I
can do to get rid of this lock?
Here's the output from sp_lock: -2 7 0 0 DB
S GRANT
Thanks in advance.
JohnOn Oct 5, 1:49 am, John Roberts
<JohnRobe...@.discussions.microsoft.com> wrote:
> splocI can't restore a database due to a locking issue. While I've killed
> the Process in Activity Monitor, I still see the database listed on the Locks
> By Object page. The Process ID is a negative number. Any ideas as to what I
> can do to get rid of this lock?
> Here's the output from sp_lock: -2 7 0 0 DB
> S GRANT
> Thanks in advance.
> John
After killing the process you may try putting database in single user
mode which would prevent application or user establishing connection.
Thanks
VS|||Thanks for the response..
When I do a select distinct req_transactionuow, req_transactionID from
syslockinfo where req_spid = -2
I see the follwing:
req_transactionuow req_transactionID
--
--
00000000-0000-0000-0000-000000000000 0
When I try to kill this UOW using the guid of all zeroes, we get the
following error:
Msg 6110, Level 16, State 1, Line 1
The distributed transaction with UOW {00000000-0000-0000-0000-000000000000}
does not exist.
Anybody out there familiar with killing orphaned transactions where the UOW
GUID is all zeros? My only solution now is to restart the service and, as
you might have imagined, that's NOT the only database running!!
Thanks in advance.
John
"vijay" wrote:
> On Oct 5, 1:49 am, John Roberts
> <JohnRobe...@.discussions.microsoft.com> wrote:
> > splocI can't restore a database due to a locking issue. While I've killed
> > the Process in Activity Monitor, I still see the database listed on the Locks
> > By Object page. The Process ID is a negative number. Any ideas as to what I
> > can do to get rid of this lock?
> >
> > Here's the output from sp_lock: -2 7 0 0 DB
> > S GRANT
> >
> > Thanks in advance.
> >
> > John
> After killing the process you may try putting database in single user
> mode which would prevent application or user establishing connection.
> Thanks
> VS
>
Killing bug in ASP.Net 2, Report server is dead, 100% guaranteed
Hi,
I posted a message to the install forum a few days ago and no answers were made so far. This bug is 100% reproducible and kills Report Server.
I am cross-posting here with the hope that someone in Microsoft will take note and eventually give a solution.
If you go to the IIS6 ReportServer VD and click on the ASP.net 2 config button, You Kill Report Server.
Complete details are in this post
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=250225&SiteID=1
Thanks,
Philippe
Hi,
We have found the solution to this issue.
Just remove this
xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"
From the WEB.config file in both ReportServer and ReportManager folders
Philippe
Killing automatically crashed sessions ??
If one of my workstations (which communicate using ODBC) crashes, I still see it as connected in Enterprise Manager under management\current activity\.
Is there anyway to kill automatically the crashed cessions?
Thkx in advanceYou could periodicaly scan the sysprocesses table and kill any connection that hasn't had activity in some predeturmined amount of time.
Is there anyway to kill automatically the crashed cessions?
Thkx in advanceYou could periodicaly scan the sysprocesses table and kill any connection that hasn't had activity in some predeturmined amount of time.
Subscribe to:
Posts (Atom)