Killing SPIDs

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

Killing SPIDs

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

"Gary" <clgary@.yahoo.com> wrote in message
news:OwXadQuaFHA.1456@.TK2MSFTNGP15.phx.gbl...
It's not setup as a linked server per se, it just uses OPENDATASOURCE.
However, I stopped MSDTC to see if that would make it stop, and it didnt.
Anyone have any other suggestions, before I restart the service? It's on a
production box and I'd rather not
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23QLdo9taFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> With linked servers, stop MSDTC and re-start it.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Gary" <clgary@.yahoo.com> wrote in message
> news:Oo4bS2taFHA.3132@.TK2MSFTNGP09.phx.gbl...
>

Killing SPIDs

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

killing a thread

Hey all,
Just wondering if there is any way to kill a thread within an sqlerver process. The thread we are trying to kill is a rollback statement that has been running for a very long time.
Any ideas ?
Thanks in advance,
KilkaC4 or nitroglycerin ?

I can't think of any reason to ever kill a rollback, other than to shutdown the server so that recovery can do the rollback faster when it has exclusive use of the database. Anything that prevents a rollback from completing essentially permanently corrupts the database.

-PatP|||Is it a result of a previous kill?|||yeah. It's the result of a previous kill.

Currently, I get this error message when I try and kill the spid.

SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

The rollback has been running for a couple hours now. Does anyone know if there is any way to see what it is rolling back ?

I'm going to bounce the server and see what happens.|||ok, so it appears the rollback was just hanging there. A bounce was all that was required. I'm still not sure if it's possible to tell what the rollback is working on. I think it would usefull to know. The reason I ask is because the spid is the result of another app working on the surface and it's quite difficult to tell what the app was doing at the time...

Cheers,
-Kilka|||You sure you're not Darkwing Duck?|||The rollback in 9 out of 10 will take longer (in many cases much longer) than the original transaction. Rollback is impossible to kill with a KILL command. The only way you can undo what KILL does is by bouncing the server, which you've already done. When the database gets recovered during the recovery process, the original rollback attempts to dismiss any originating transaction (basically ignoring the altered data pages recorded in the transaction log) and just moves on with what was actually committed and recorded in the trx log..|||Maybe there's some undocumented way of doing it? I'm in the situation right now that I'm waiting for a rollback that will take hours, and I just want to re-create the database from an old backup anyway. However, there are production databases on the same server so I can't shut down anything outside the particular database.sql

killing a process shows 0% completion time

Hello,
I'm running Sql server 2005 and I've noticed that when I kill a
process, it always shows
"Estimated rollback completion: 0%. Estimated time remaining: 0
seconds."
Even though the process does kill successfully, these numbers never
change. Is there some setting I have to change or what?
Can anyone help?
Thanks.When you kill a running process/transaction, the system will have to go
through and rollback the transaction. There is nothing you can do here other
than to wait for completion.
If you force a system restart, the transaction will be re-rollbacked on the
next restart.
--
-oj
<clemlau@.yahoo.com> wrote in message
news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
> Hello,
> I'm running Sql server 2005 and I've noticed that when I kill a
> process, it always shows
>
> "Estimated rollback completion: 0%. Estimated time remaining: 0
> seconds."
>
> Even though the process does kill successfully, these numbers never
> change. Is there some setting I have to change or what?
> Can anyone help?
> Thanks.
>|||This is a multi-part message in MIME format.
--010208050704050705070309
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
How are you getting the completion figure? Are you using "KILL <spid>
WITH STATUSONLY"?
--
*mike hodgson*
http://sqlnerd.blogspot.com
clemlau@.yahoo.com wrote:
>Hello,
>I'm running Sql server 2005 and I've noticed that when I kill a
>process, it always shows
>
>"Estimated rollback completion: 0%. Estimated time remaining: 0
>seconds."
>
>Even though the process does kill successfully, these numbers never
>change. Is there some setting I have to change or what?
>Can anyone help?
>Thanks.
>
>
--010208050704050705070309
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>How are you getting the completion figure? Are you using "KILL
<spid> WITH STATUSONLY"?</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:clemlau@.yahoo.com">clemlau@.yahoo.com</a> wrote:
<blockquote
cite="mid1151442931.224581.203680@.b68g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">Hello,
I'm running Sql server 2005 and I've noticed that when I kill a
process, it always shows
"Estimated rollback completion: 0%. Estimated time remaining: 0
seconds."
Even though the process does kill successfully, these numbers never
change. Is there some setting I have to change or what?
Can anyone help?
Thanks.
</pre>
</blockquote>
</body>
</html>
--010208050704050705070309--|||And just to add to the doom and gloom, the rollback is part of the database
recovery. No connections to the database will be allowed until the rollback
is complete. I have seen (and survived with job intact) a four-hour unwind
on a restart, so this can get very bad.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"oj" <nospam_ojngo@.home.com> wrote in message
news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> When you kill a running process/transaction, the system will have to go
> through and rollback the transaction. There is nothing you can do here
> other than to wait for completion.
> If you force a system restart, the transaction will be re-rollbacked on
> the next restart.
> --
> -oj
>
> <clemlau@.yahoo.com> wrote in message
> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
>> Hello,
>> I'm running Sql server 2005 and I've noticed that when I kill a
>> process, it always shows
>>
>> "Estimated rollback completion: 0%. Estimated time remaining: 0
>> seconds."
>>
>> Even though the process does kill successfully, these numbers never
>> change. Is there some setting I have to change or what?
>> Can anyone help?
>> Thanks.
>|||it's sql2k5! ;-)
the db should be avail as soons as redo is done.
--
-oj
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
> And just to add to the doom and gloom, the rollback is part of the
> database recovery. No connections to the database will be allowed until
> the rollback is complete. I have seen (and survived with job intact) a
> four-hour unwind on a restart, so this can get very bad.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
>> When you kill a running process/transaction, the system will have to go
>> through and rollback the transaction. There is nothing you can do here
>> other than to wait for completion.
>> If you force a system restart, the transaction will be re-rollbacked on
>> the next restart.
>> --
>> -oj
>>
>> <clemlau@.yahoo.com> wrote in message
>> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
>> Hello,
>> I'm running Sql server 2005 and I've noticed that when I kill a
>> process, it always shows
>>
>> "Estimated rollback completion: 0%. Estimated time remaining: 0
>> seconds."
>>
>> Even though the process does kill successfully, these numbers never
>> change. Is there some setting I have to change or what?
>> Can anyone help?
>> Thanks.
>>
>|||You are correct.
Good catch. Thanks,
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...
> it's sql2k5! ;-)
> the db should be avail as soons as redo is done.
> --
> -oj
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
>> And just to add to the doom and gloom, the rollback is part of the
>> database recovery. No connections to the database will be allowed until
>> the rollback is complete. I have seen (and survived with job intact) a
>> four-hour unwind on a restart, so this can get very bad.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "oj" <nospam_ojngo@.home.com> wrote in message
>> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
>> When you kill a running process/transaction, the system will have to go
>> through and rollback the transaction. There is nothing you can do here
>> other than to wait for completion.
>> If you force a system restart, the transaction will be re-rollbacked on
>> the next restart.
>> --
>> -oj
>>
>> <clemlau@.yahoo.com> wrote in message
>> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
>> Hello,
>> I'm running Sql server 2005 and I've noticed that when I kill a
>> process, it always shows
>>
>> "Estimated rollback completion: 0%. Estimated time remaining: 0
>> seconds."
>>
>> Even though the process does kill successfully, these numbers never
>> change. Is there some setting I have to change or what?
>> Can anyone help?
>> Thanks.
>>
>>
>|||I'm running kill spid with statusonly. This result always shows 0%
completion. Whether it takes 5 seconds or 8 hours to rollback, I
always see 0% completion. (I had a process today that I had to kill
after running for 6 hours and it took 8 hours to kill but I had no idea
about it's progress.)
In sql 2000, this worked everytime I killed a process. I could see the
% changing and the estimated time to complete changing.
Any ideas?
Thanks,
Clem
Geoff N. Hiten wrote:
> You are correct.
> Good catch. Thanks,
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...
> > it's sql2k5! ;-)
> >
> > the db should be avail as soons as redo is done.
> >
> > --
> > -oj
> >
> >
> >
> > "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> > news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
> >> And just to add to the doom and gloom, the rollback is part of the
> >> database recovery. No connections to the database will be allowed until
> >> the rollback is complete. I have seen (and survived with job intact) a
> >> four-hour unwind on a restart, so this can get very bad.
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior Database Administrator
> >> Microsoft SQL Server MVP
> >>
> >>
> >>
> >> "oj" <nospam_ojngo@.home.com> wrote in message
> >> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> >> When you kill a running process/transaction, the system will have to go
> >> through and rollback the transaction. There is nothing you can do here
> >> other than to wait for completion.
> >>
> >> If you force a system restart, the transaction will be re-rollbacked on
> >> the next restart.
> >>
> >> --
> >> -oj
> >>
> >>
> >>
> >> <clemlau@.yahoo.com> wrote in message
> >> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
> >> Hello,
> >>
> >> I'm running Sql server 2005 and I've noticed that when I kill a
> >> process, it always shows
> >>
> >>
> >> "Estimated rollback completion: 0%. Estimated time remaining: 0
> >> seconds."
> >>
> >>
> >> Even though the process does kill successfully, these numbers never
> >> change. Is there some setting I have to change or what?
> >>
> >> Can anyone help?
> >>
> >> Thanks.
> >>
> >>
> >>
> >>
> >>
> >
> >|||> the db should be avail as soons as redo is done.
On Enterprise and Developer Edition... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"oj" <nospam_ojngo@.home.com> wrote in message news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...
> it's sql2k5! ;-)
> the db should be avail as soons as redo is done.
> --
> -oj
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
>> And just to add to the doom and gloom, the rollback is part of the
>> database recovery. No connections to the database will be allowed until
>> the rollback is complete. I have seen (and survived with job intact) a
>> four-hour unwind on a restart, so this can get very bad.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "oj" <nospam_ojngo@.home.com> wrote in message
>> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
>> When you kill a running process/transaction, the system will have to go
>> through and rollback the transaction. There is nothing you can do here
>> other than to wait for completion.
>> If you force a system restart, the transaction will be re-rollbacked on
>> the next restart.
>> --
>> -oj
>>
>> <clemlau@.yahoo.com> wrote in message
>> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
>> Hello,
>> I'm running Sql server 2005 and I've noticed that when I kill a
>> process, it always shows
>>
>> "Estimated rollback completion: 0%. Estimated time remaining: 0
>> seconds."
>>
>> Even though the process does kill successfully, these numbers never
>> change. Is there some setting I have to change or what?
>> Can anyone help?
>> Thanks.
>>
>>
>|||that's your problem. you're not actually killing the spid.
"WITH STATUSONLY
Generates a progress report on a given spid or UOW that is being rolled
back due to an earlier KILL statement. KILL WITH STATUSONLY does not
terminate or roll back the spid or UOW, it only displays the current
progress of the roll back."
-oj
<clemlau@.yahoo.com> wrote in message
news:1151467451.774578.30370@.i40g2000cwc.googlegroups.com...
>
> I'm running kill spid with statusonly. This result always shows 0%
> completion. Whether it takes 5 seconds or 8 hours to rollback, I
> always see 0% completion. (I had a process today that I had to kill
> after running for 6 hours and it took 8 hours to kill but I had no idea
> about it's progress.)
> In sql 2000, this worked everytime I killed a process. I could see the
> % changing and the estimated time to complete changing.
> Any ideas?
>
> Thanks,
> Clem
>
> Geoff N. Hiten wrote:
>> You are correct.
>> Good catch. Thanks,
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "oj" <nospam_ojngo@.home.com> wrote in message
>> news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...
>> > it's sql2k5! ;-)
>> >
>> > the db should be avail as soons as redo is done.
>> >
>> > --
>> > -oj
>> >
>> >
>> >
>> > "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
>> > news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
>> >> And just to add to the doom and gloom, the rollback is part of the
>> >> database recovery. No connections to the database will be allowed
>> >> until
>> >> the rollback is complete. I have seen (and survived with job intact)
>> >> a
>> >> four-hour unwind on a restart, so this can get very bad.
>> >>
>> >> --
>> >> Geoff N. Hiten
>> >> Senior Database Administrator
>> >> Microsoft SQL Server MVP
>> >>
>> >>
>> >>
>> >> "oj" <nospam_ojngo@.home.com> wrote in message
>> >> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
>> >> When you kill a running process/transaction, the system will have to
>> >> go
>> >> through and rollback the transaction. There is nothing you can do
>> >> here
>> >> other than to wait for completion.
>> >>
>> >> If you force a system restart, the transaction will be re-rollbacked
>> >> on
>> >> the next restart.
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >>
>> >> <clemlau@.yahoo.com> wrote in message
>> >> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
>> >> Hello,
>> >>
>> >> I'm running Sql server 2005 and I've noticed that when I kill a
>> >> process, it always shows
>> >>
>> >>
>> >> "Estimated rollback completion: 0%. Estimated time remaining: 0
>> >> seconds."
>> >>
>> >>
>> >> Even though the process does kill successfully, these numbers never
>> >> change. Is there some setting I have to change or what?
>> >>
>> >> Can anyone help?
>> >>
>> >> Thanks.
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>|||so true. ;-)
--
-oj
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23NQm%238nmGHA.4212@.TK2MSFTNGP03.phx.gbl...
>> the db should be avail as soons as redo is done.
> On Enterprise and Developer Edition... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||This is a multi-part message in MIME format.
--060106070200070902040203
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I think you're missing the point - the OP has already issued a KILL
statement against the SPID in question and then, after that, he runs
"KILL <spid> WITH STATUSONLY" to see how the rollback is going, but the
figure that gets reported is always 0% on the rollback. [Is that right,
Clem?]
While I haven't analysed the situation much in SQL 2005, I have seen
similar behaviour. It made me curious at the time, but not enough to
find out what was going on, given that, on our SQL 2005 box, we don't
kill much (at least not yet). Sorry to be not much help at this time.
--
*mike hodgson*
http://sqlnerd.blogspot.com
oj wrote:
>that's your problem. you're not actually killing the spid.
>"WITH STATUSONLY
> Generates a progress report on a given spid or UOW that is being rolled
>back due to an earlier KILL statement. KILL WITH STATUSONLY does not
>terminate or roll back the spid or UOW, it only displays the current
>progress of the roll back."
>
>
--060106070200070902040203
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I think you're missing the point - the OP has already issued a KILL
statement against the SPID in question and then, after that, he runs
"KILL <spid> WITH STATUSONLY" to see how the rollback is going,
but the figure that gets reported is always 0% on the rollback. [Is
that right, Clem?]<br>
<br>
While I haven't analysed the situation much in SQL 2005, I have seen
similar behaviour. It made me curious at the time, but not enough to
find out what was going on, given that, on our SQL 2005 box, we don't
kill much (at least not yet). Sorry to be not much help at this time.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
oj wrote:
<blockquote cite="mid%23c$BJRomGHA.2316@.TK2MSFTNGP04.phx.gbl"
type="cite">
<pre wrap="">that's your problem. you're not actually killing the spid.
"WITH STATUSONLY
Generates a progress report on a given spid or UOW that is being rolled
back due to an earlier KILL statement. KILL WITH STATUSONLY does not
terminate or roll back the spid or UOW, it only displays the current
progress of the roll back."
</pre>
</blockquote>
</body>
</html>
--060106070200070902040203--|||Yes you''re correct Mike. I just want to see the progress of the
rollback and it's always at 0%.
Mike Hodgson wrote:
> I think you're missing the point - the OP has already issued a KILL
> statement against the SPID in question and then, after that, he runs
> "KILL <spid> WITH STATUSONLY" to see how the rollback is going, but the
> figure that gets reported is always 0% on the rollback. [Is that right,
> Clem?]
> While I haven't analysed the situation much in SQL 2005, I have seen
> similar behaviour. It made me curious at the time, but not enough to
> find out what was going on, given that, on our SQL 2005 box, we don't
> kill much (at least not yet). Sorry to be not much help at this time.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> oj wrote:
> >that's your problem. you're not actually killing the spid.
> >
> >"WITH STATUSONLY
> > Generates a progress report on a given spid or UOW that is being rolled
> >back due to an earlier KILL statement. KILL WITH STATUSONLY does not
> >terminate or roll back the spid or UOW, it only displays the current
> >progress of the roll back."
> >
> >
> >
> >
> --060106070200070902040203
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 1688
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>I think you're missing the point - the OP has already issued a KILL
> statement against the SPID in question and then, after that, he runs
> "KILL <spid> WITH STATUSONLY" to see how the rollback is going,
> but the figure that gets reported is always 0% on the rollback. [Is
> that right, Clem?]<br>
> <br>
> While I haven't analysed the situation much in SQL 2005, I have seen
> similar behaviour. It made me curious at the time, but not enough to
> find out what was going on, given that, on our SQL 2005 box, we don't
> kill much (at least not yet). Sorry to be not much help at this time.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> oj wrote:
> <blockquote cite="mid%23c$BJRomGHA.2316@.TK2MSFTNGP04.phx.gbl"
> type="cite">
> <pre wrap="">that's your problem. you're not actually killing the spid.
> "WITH STATUSONLY
> Generates a progress report on a given spid or UOW that is being rolled
> back due to an earlier KILL statement. KILL WITH STATUSONLY does not
> terminate or roll back the spid or UOW, it only displays the current
> progress of the roll back."
>
> </pre>
> </blockquote>
> </body>
> </html>
> --060106070200070902040203--|||<clemlau@.yahoo.com> wrote in message
news:1151467451.774578.30370@.i40g2000cwc.googlegroups.com...
>
> I'm running kill spid with statusonly. This result always shows 0%
> completion. Whether it takes 5 seconds or 8 hours to rollback, I
> always see 0% completion. (I had a process today that I had to kill
> after running for 6 hours and it took 8 hours to kill but I had no idea
> about it's progress.)
> In sql 2000, this worked everytime I killed a process. I could see the
> % changing and the estimated time to complete changing.
Somethings just don't roll back nicely.
Especially anything calling an XP procedure or cross-database calls.
I've also seen a few cases (with SQL2000) where if the client disconnects
non-cleanly, the rollback may show 100% complete, but the SPID never goes
away.
> Any ideas?
>
> Thanks,
> Clem
>
> Geoff N. Hiten wrote:
> > You are correct.
> >
> > Good catch. Thanks,
> >
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> >
> >
> > "oj" <nospam_ojngo@.home.com> wrote in message
> > news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...
> > > it's sql2k5! ;-)
> > >
> > > the db should be avail as soons as redo is done.
> > >
> > > --
> > > -oj
> > >
> > >
> > >
> > > "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> > > news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
> > >> And just to add to the doom and gloom, the rollback is part of the
> > >> database recovery. No connections to the database will be allowed
until
> > >> the rollback is complete. I have seen (and survived with job intact)
a
> > >> four-hour unwind on a restart, so this can get very bad.
> > >>
> > >> --
> > >> Geoff N. Hiten
> > >> Senior Database Administrator
> > >> Microsoft SQL Server MVP
> > >>
> > >>
> > >>
> > >> "oj" <nospam_ojngo@.home.com> wrote in message
> > >> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> > >> When you kill a running process/transaction, the system will have to
go
> > >> through and rollback the transaction. There is nothing you can do
here
> > >> other than to wait for completion.
> > >>
> > >> If you force a system restart, the transaction will be re-rollbacked
on
> > >> the next restart.
> > >>
> > >> --
> > >> -oj
> > >>
> > >>
> > >>
> > >> <clemlau@.yahoo.com> wrote in message
> > >> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
> > >> Hello,
> > >>
> > >> I'm running Sql server 2005 and I've noticed that when I kill a
> > >> process, it always shows
> > >>
> > >>
> > >> "Estimated rollback completion: 0%. Estimated time remaining: 0
> > >> seconds."
> > >>
> > >>
> > >> Even though the process does kill successfully, these numbers
never
> > >> change. Is there some setting I have to change or what?
> > >>
> > >> Can anyone help?
> > >>
> > >> Thanks.
> > >>
> > >>
> > >>
> > >>
> > >>
> > >
> > >
>

killing a process shows 0% completion time

When you kill a running process/transaction, the system will have to go
through and rollback the transaction. There is nothing you can do here other
than to wait for completion.
If you force a system restart, the transaction will be re-rollbacked on the
next restart.
-oj
<clemlau@.yahoo.com> wrote in message
news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
> Hello,
> I'm running Sql server 2005 and I've noticed that when I kill a
> process, it always shows
>
> "Estimated rollback completion: 0%. Estimated time remaining: 0
> seconds."
>
> Even though the process does kill successfully, these numbers never
> change. Is there some setting I have to change or what?
> Can anyone help?
> Thanks.
>How are you getting the completion figure? Are you using "KILL <spid>
WITH STATUSONLY"?
*mike hodgson*
http://sqlnerd.blogspot.com
clemlau@.yahoo.com wrote:

>Hello,
>I'm running Sql server 2005 and I've noticed that when I kill a
>process, it always shows
>
>"Estimated rollback completion: 0%. Estimated time remaining: 0
>seconds."
>
>Even though the process does kill successfully, these numbers never
>change. Is there some setting I have to change or what?
>Can anyone help?
>Thanks.
>
>|||And just to add to the doom and gloom, the rollback is part of the database
recovery. No connections to the database will be allowed until the rollback
is complete. I have seen (and survived with job intact) a four-hour unwind
on a restart, so this can get very bad.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"oj" <nospam_ojngo@.home.com> wrote in message
news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> When you kill a running process/transaction, the system will have to go
> through and rollback the transaction. There is nothing you can do here
> other than to wait for completion.
> If you force a system restart, the transaction will be re-rollbacked on
> the next restart.
> --
> -oj
>
> <clemlau@.yahoo.com> wrote in message
> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
>|||it's sql2k5! ;-)
the db should be avail as soons as redo is done.
-oj
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
> And just to add to the doom and gloom, the rollback is part of the
> database recovery. No connections to the database will be allowed until
> the rollback is complete. I have seen (and survived with job intact) a
> four-hour unwind on a restart, so this can get very bad.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
>|||Hello,
I'm running Sql server 2005 and I've noticed that when I kill a
process, it always shows
"Estimated rollback completion: 0%. Estimated time remaining: 0
seconds."
Even though the process does kill successfully, these numbers never
change. Is there some setting I have to change or what?
Can anyone help?
Thanks.|||When you kill a running process/transaction, the system will have to go
through and rollback the transaction. There is nothing you can do here other
than to wait for completion.
If you force a system restart, the transaction will be re-rollbacked on the
next restart.
-oj
<clemlau@.yahoo.com> wrote in message
news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
> Hello,
> I'm running Sql server 2005 and I've noticed that when I kill a
> process, it always shows
>
> "Estimated rollback completion: 0%. Estimated time remaining: 0
> seconds."
>
> Even though the process does kill successfully, these numbers never
> change. Is there some setting I have to change or what?
> Can anyone help?
> Thanks.
>|||How are you getting the completion figure? Are you using "KILL <spid>
WITH STATUSONLY"?
*mike hodgson*
http://sqlnerd.blogspot.com
clemlau@.yahoo.com wrote:

>Hello,
>I'm running Sql server 2005 and I've noticed that when I kill a
>process, it always shows
>
>"Estimated rollback completion: 0%. Estimated time remaining: 0
>seconds."
>
>Even though the process does kill successfully, these numbers never
>change. Is there some setting I have to change or what?
>Can anyone help?
>Thanks.
>
>|||And just to add to the doom and gloom, the rollback is part of the database
recovery. No connections to the database will be allowed until the rollback
is complete. I have seen (and survived with job intact) a four-hour unwind
on a restart, so this can get very bad.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"oj" <nospam_ojngo@.home.com> wrote in message
news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> When you kill a running process/transaction, the system will have to go
> through and rollback the transaction. There is nothing you can do here
> other than to wait for completion.
> If you force a system restart, the transaction will be re-rollbacked on
> the next restart.
> --
> -oj
>
> <clemlau@.yahoo.com> wrote in message
> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
>|||You are correct.
Good catch. Thanks,
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...
> it's sql2k5! ;-)
> the db should be avail as soons as redo is done.
> --
> -oj
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
>|||I'm running kill spid with statusonly. This result always shows 0%
completion. Whether it takes 5 seconds or 8 hours to rollback, I
always see 0% completion. (I had a process today that I had to kill
after running for 6 hours and it took 8 hours to kill but I had no idea
about it's progress.)
In sql 2000, this worked everytime I killed a process. I could see the
% changing and the estimated time to complete changing.
Any ideas?
Thanks,
Clem
Geoff N. Hiten wrote:[vbcol=seagreen]
> You are correct.
> Good catch. Thanks,
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...

Killing a hung process..

A user connection which was killed went into RUNNABLE status with KILLED/ROL
LBACK cmd when we monitored with SP_WHO2.It looks like the process was hung
and no activity in CPU or I/O.How do we get rid of that process completely w
ithout restarting sql serve
r?Hi,
Normally when you kill a runnable process which does any (Update / Insert /
delete) it does a ROLLBACK to ensure that things are back as old. After
rollback that process will be removed from the process list automatically.
Thanks
Hari
MCDBA
"Mano" <anonymous@.discussions.microsoft.com> wrote in message
news:AAF19D66-EE57-4C0D-9F84-F5841FF53459@.microsoft.com...

quote:

---

> A user connection which was killed went into RUNNABLE status with

---

KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process
was hung and no activity in CPU or I/O.How do we get rid of that process
completely without restarting sql server?

Killing a hung process..

A user connection which was killed went into RUNNABLE status with KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process was hung and no activity in CPU or I/O.How do we get rid of that process completely without restarting sql server?Hi,
Normally when you kill a runnable process which does any (Update / Insert /
delete) it does a ROLLBACK to ensure that things are back as old. After
rollback that process will be removed from the process list automatically.
Thanks
Hari
MCDBA
"Mano" <anonymous@.discussions.microsoft.com> wrote in message
news:AAF19D66-EE57-4C0D-9F84-F5841FF53459@.microsoft.com...
> A user connection which was killed went into RUNNABLE status with
KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process
was hung and no activity in CPU or I/O.How do we get rid of that process
completely without restarting sql server?

Killing a hung process

A user connection which was killed went into RUNNABLE status with KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process was hung and no activity in CPU or I/O.How do we get rid of that process completely without restarting the sql server?A spid can get in a state where it can't be killed. In fact some would say
that KILL should be renamed WOUND :-)
One of the more common reasons that a process can'r be killed is if it
called an extended proc or launches an externall process that has some way
hung. Examples include xp_sendmail or xp_cmdshell. This will hang and if you
kill it it will still stay there in sysprocesses until you restart the
server. You can run KILL spid WITH STATUSONLY to get a report of how far
along the rollback is but for these unkillable spids it will report 100%
complete but never be able to kill the spid because it launched an external
process. The only way to get rid of these spids is to restart the SQL
Service however, if they are not holding any locks or keeping a transaction
open, they don't tend to do a lot of harm
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mano" <anonymous@.discussions.microsoft.com> wrote in message
news:D32E89EF-6D00-423D-81D5-027F5390ACD2@.microsoft.com...
> A user connection which was killed went into RUNNABLE status with
KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process
was hung and no activity in CPU or I/O.How do we get rid of that process
completely without restarting the sql server?sql

Killing a hung process

A user connection which was killed went into RUNNABLE status with KILLED/ROL
LBACK cmd when we monitored with SP_WHO2.It looks like the process was hung
and no activity in CPU or I/O.How do we get rid of that process completely w
ithout restarting the sql s
erver?A spid can get in a state where it can't be killed. In fact some would say
that KILL should be renamed WOUND :-)
One of the more common reasons that a process can'r be killed is if it
called an extended proc or launches an externall process that has some way
hung. Examples include xp_sendmail or xp_cmdshell. This will hang and if you
kill it it will still stay there in sysprocesses until you restart the
server. You can run KILL spid WITH STATUSONLY to get a report of how far
along the rollback is but for these unkillable spids it will report 100%
complete but never be able to kill the spid because it launched an external
process. The only way to get rid of these spids is to restart the SQL
Service however, if they are not holding any locks or keeping a transaction
open, they don't tend to do a lot of harm
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mano" <anonymous@.discussions.microsoft.com> wrote in message
news:D32E89EF-6D00-423D-81D5-027F5390ACD2@.microsoft.com...

quote:

---

> A user connection which was killed went into RUNNABLE status with

---

KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process
was hung and no activity in CPU or I/O.How do we get rid of that process
completely without restarting the sql server?

Killing a DBCC DBREINDEX - will this cause a massive rollback

I recall several instances where I killed a DBREINDEX after 20 minutes of
execution and it seemed to cancel surprisingly fast (I was expecting a
massive rollback)
We now have a situation where a DBREINDEX has been running over 12 hours.
If I were to kill this - could it be assumed that it would just become
available immediately or is there a chance of a BIG rollback (which could
possibly take another 12 hours)?
Thanks in advanceIt depends when you cancel it and what you've asked it to do.
If you have a dbreindex of a single very large index, and cancel it while
its still building the new index, it should cancel pretty fast as all it
does is deallocate all the new pages. If you cancel it while it's deleting
the old index, then it has to rollback all the deletes again.
If you have a dbreindex that's rebuilding 10 indexes, and you cancel it
after its done 5 of them, it will have to rollback the deletes of the 5 old
indexes, which will take time proportional to their size.
Several things to consider: why are you rebuilding and have you considered
using DBCC INDEXDEFRAG instead?
Checkout the whitepaper below for more info:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJT" <TJT@.nospam.com> wrote in message
news:%23Hi2ZwjuFHA.1256@.TK2MSFTNGP09.phx.gbl...
>I recall several instances where I killed a DBREINDEX after 20 minutes of
> execution and it seemed to cancel surprisingly fast (I was expecting a
> massive rollback)
> We now have a situation where a DBREINDEX has been running over 12 hours.
> If I were to kill this - could it be assumed that it would just become
> available immediately or is there a chance of a BIG rollback (which could
> possibly take another 12 hours)?
> Thanks in advance
>

Killing a DBCC DBREINDEX - will this cause a massive rollback

I recall several instances where I killed a DBREINDEX after 20 minutes of
execution and it seemed to cancel surprisingly fast (I was expecting a
massive rollback)
We now have a situation where a DBREINDEX has been running over 12 hours.
If I were to kill this - could it be assumed that it would just become
available immediately or is there a chance of a BIG rollback (which could
possibly take another 12 hours)?
Thanks in advance
It depends when you cancel it and what you've asked it to do.
If you have a dbreindex of a single very large index, and cancel it while
its still building the new index, it should cancel pretty fast as all it
does is deallocate all the new pages. If you cancel it while it's deleting
the old index, then it has to rollback all the deletes again.
If you have a dbreindex that's rebuilding 10 indexes, and you cancel it
after its done 5 of them, it will have to rollback the deletes of the 5 old
indexes, which will take time proportional to their size.
Several things to consider: why are you rebuilding and have you considered
using DBCC INDEXDEFRAG instead?
Checkout the whitepaper below for more info:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJT" <TJT@.nospam.com> wrote in message
news:%23Hi2ZwjuFHA.1256@.TK2MSFTNGP09.phx.gbl...
>I recall several instances where I killed a DBREINDEX after 20 minutes of
> execution and it seemed to cancel surprisingly fast (I was expecting a
> massive rollback)
> We now have a situation where a DBREINDEX has been running over 12 hours.
> If I were to kill this - could it be assumed that it would just become
> available immediately or is there a chance of a BIG rollback (which could
> possibly take another 12 hours)?
> Thanks in advance
>

Killing a DBCC DBREINDEX - will this cause a massive rollback

I recall several instances where I killed a DBREINDEX after 20 minutes of
execution and it seemed to cancel surprisingly fast (I was expecting a
massive rollback)
We now have a situation where a DBREINDEX has been running over 12 hours.
If I were to kill this - could it be assumed that it would just become
available immediately or is there a chance of a BIG rollback (which could
possibly take another 12 hours)?
Thanks in advanceIt depends when you cancel it and what you've asked it to do.
If you have a dbreindex of a single very large index, and cancel it while
its still building the new index, it should cancel pretty fast as all it
does is deallocate all the new pages. If you cancel it while it's deleting
the old index, then it has to rollback all the deletes again.
If you have a dbreindex that's rebuilding 10 indexes, and you cancel it
after its done 5 of them, it will have to rollback the deletes of the 5 old
indexes, which will take time proportional to their size.
Several things to consider: why are you rebuilding and have you considered
using DBCC INDEXDEFRAG instead?
Checkout the whitepaper below for more info:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJT" <TJT@.nospam.com> wrote in message
news:%23Hi2ZwjuFHA.1256@.TK2MSFTNGP09.phx.gbl...
>I recall several instances where I killed a DBREINDEX after 20 minutes of
> execution and it seemed to cancel surprisingly fast (I was expecting a
> massive rollback)
> We now have a situation where a DBREINDEX has been running over 12 hours.
> If I were to kill this - could it be assumed that it would just become
> available immediately or is there a chance of a BIG rollback (which could
> possibly take another 12 hours)?
> Thanks in advance
>

killed/rollback stuck on object_name(99)

Hello:
I have a process that's been stuck for two days.. It's a stored procedure
that runs as part of a scheduled SqlAgent job. I tried to kill the process
which put it into a rollback. Kill with statusonly returns:
SPID 52: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
I ran dbcc page for the resource that is listed in the wait type
(PAGEIOLATCH_UP)
and it points to Obj_id 99. Running "select object_name(99)" returns the
object name "Allocation".
Does anyone know what this means and how to allow the rollback to complete?
This spid blocks other processess that try to run in the affected database.
Even Enterprise Manager is blocked. Can't refresh table list or procedure
list in EM. Current activity times out. I can use sp_who2 to see active
processes.hi,
Right, try again using this:
KILL <your_process> WITH STATUSONLY
Because of your process has been running a long time the rollback will take
a lot of time (not the same, of course, but a lof anyway)
Rollback is undoing changes and transactions commited
Current location: Alicante (ES)
"tthrone" wrote:

> Hello:
> I have a process that's been stuck for two days.. It's a stored procedure
> that runs as part of a scheduled SqlAgent job. I tried to kill the proces
s
> which put it into a rollback. Kill with statusonly returns:
> SPID 52: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> I ran dbcc page for the resource that is listed in the wait type
> (PAGEIOLATCH_UP)
> and it points to Obj_id 99. Running "select object_name(99)" returns the
> object name "Allocation".
> Does anyone know what this means and how to allow the rollback to complete
?
> This spid blocks other processess that try to run in the affected database
.
> Even Enterprise Manager is blocked. Can't refresh table list or procedure
> list in EM. Current activity times out. I can use sp_who2 to see active
> processes.
>|||Hi Enric,
I did that. It returns:
> SPID 52: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
>
It's been returning the same thing for two days. It doesn't appear to be
making any progress on the rollback. The original process should have done
123,000 row inserts on a previously empty table. I can't imagine 123k rows
should take 2 days to rollback. I think it's totally stuck and idle.
"Enric" wrote:
> hi,
> Right, try again using this:
> KILL <your_process> WITH STATUSONLY
> Because of your process has been running a long time the rollback will tak
e
> a lot of time (not the same, of course, but a lof anyway)
> Rollback is undoing changes and transactions commited
> --
> Current location: Alicante (ES)
>
> "tthrone" wrote:
>|||First, try to find out what application and T-SQL statement caused this
situation so perhaps it won't repeat:
DBCC INPUTBUFFER (spid) will display the last T-SQL statement sent by the
client application owning this SPID.
SP_LOCK (spid) will list information about what specific objects the SPID
currently has locked and what type of lock (table, page, etc.).
Next, try to diagnose what is going on with your server hard disks, memory,
etc. that may have caused this unusual cirsumstance. If the server is
running critically low on disk space, this can cause problems when
attempting rollback a large transaction. Also, go into the windows
management console and review the event logs for possible evidence.
This article describes how get more detailed information about the current
status of the SPID:
http://support.microsoft.com/defaul...kb;en-us;171224
For example, the Process Status Structure (PSS) has the following values:
0x4000 -- Delay KILL and ATTENTION signals if inside a critical section
0x2000 -- Process is being killed
0x800 -- Process is in backout, thus cannot be chosen as deadlock victim
0x400 -- Process has received an ATTENTION signal, and has responded by
raising an internal exception
0x100 -- Process in the middle of a single statement transaction
0x80 -- Process is involved in multi-database transaction
0x8 -- Process is currently executing a trigger
0x2 -- Process has received KILL command
0x1 -- Process has received an ATTENTION signal
This article describes how to identify and troubleshoot an orphaned
connection:
http://support.microsoft.com/kb/137983/EN-US/
If the SPID can't be killed, then:
1. stop the SQL Server service (no need to reboot)
2. using Windows Explorer, move the data and transaction log file(s) to
another location
3. re-start the service
4. restore the database from the most recent backup
"tthrone" <tthrone@.discussions.microsoft.com> wrote in message
news:D7361B71-3C5A-41CE-A4D0-68685B910E3E@.microsoft.com...
> Hello:
> I have a process that's been stuck for two days.. It's a stored procedure
> that runs as part of a scheduled SqlAgent job. I tried to kill the
> process
> which put it into a rollback. Kill with statusonly returns:
> SPID 52: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> I ran dbcc page for the resource that is listed in the wait type
> (PAGEIOLATCH_UP)
> and it points to Obj_id 99. Running "select object_name(99)" returns the
> object name "Allocation".
> Does anyone know what this means and how to allow the rollback to
> complete?
> This spid blocks other processess that try to run in the affected
> database.
> Even Enterprise Manager is blocked. Can't refresh table list or procedure
> list in EM. Current activity times out. I can use sp_who2 to see active
> processes.
>|||Thanks JT. I know some answers to questions/issues you listed. I know the
transation that was in-flight, but I don't know why it stuck. Still can't
figure out why it remains stuck, but I found something interesting in the
process of doing some of what you suggested.
For one, I see this spid blocks some of my attempts to use sysobjects. I
mentioned that I can't refresh procedures or tables in EM on this database.
I think that's why. I have it narrowed down to one (maybe a few) affected
tables. I can query sysobjects so long as I don't try to read certain rows.
Not sure how that happened!
I think I'm going to have to try your suggestion about stopping the service
and restoring.
Thanks for the help.
"JT" wrote:

> First, try to find out what application and T-SQL statement caused this
> situation so perhaps it won't repeat:
> DBCC INPUTBUFFER (spid) will display the last T-SQL statement sent by the
> client application owning this SPID.
> SP_LOCK (spid) will list information about what specific objects the SPID
> currently has locked and what type of lock (table, page, etc.).
> Next, try to diagnose what is going on with your server hard disks, memory
,
> etc. that may have caused this unusual cirsumstance. If the server is
> running critically low on disk space, this can cause problems when
> attempting rollback a large transaction. Also, go into the windows
> management console and review the event logs for possible evidence.
> This article describes how get more detailed information about the current
> status of the SPID:
> http://support.microsoft.com/defaul...kb;en-us;171224
> For example, the Process Status Structure (PSS) has the following values:
> 0x4000 -- Delay KILL and ATTENTION signals if inside a critical section
> 0x2000 -- Process is being killed
> 0x800 -- Process is in backout, thus cannot be chosen as deadlock victim
> 0x400 -- Process has received an ATTENTION signal, and has responded by
> raising an internal exception
> 0x100 -- Process in the middle of a single statement transaction
> 0x80 -- Process is involved in multi-database transaction
> 0x8 -- Process is currently executing a trigger
> 0x2 -- Process has received KILL command
> 0x1 -- Process has received an ATTENTION signal
> This article describes how to identify and troubleshoot an orphaned
> connection:
> http://support.microsoft.com/kb/137983/EN-US/
> If the SPID can't be killed, then:
> 1. stop the SQL Server service (no need to reboot)
> 2. using Windows Explorer, move the data and transaction log file(s) to
> another location
> 3. re-start the service
> 4. restore the database from the most recent backup
>
> "tthrone" <tthrone@.discussions.microsoft.com> wrote in message
> news:D7361B71-3C5A-41CE-A4D0-68685B910E3E@.microsoft.com...
>
>|||When querying sysobjects (or any other blocked table), you can get around
the locks by changing the isolation level to read uncommitted data. However,
this should not be used in a production system except perhaps in some
reporting situations.
set transaction isolation level read uncommitted
select * from sysobjects
"tthrone" <tthrone@.discussions.microsoft.com> wrote in message
news:1C4B57D4-1894-4688-8E32-6157E28AD503@.microsoft.com...
> Thanks JT. I know some answers to questions/issues you listed. I know
> the
> transation that was in-flight, but I don't know why it stuck. Still can't
> figure out why it remains stuck, but I found something interesting in the
> process of doing some of what you suggested.
> For one, I see this spid blocks some of my attempts to use sysobjects. I
> mentioned that I can't refresh procedures or tables in EM on this
> database.
> I think that's why. I have it narrowed down to one (maybe a few) affected
> tables. I can query sysobjects so long as I don't try to read certain
> rows.
> Not sure how that happened!
> I think I'm going to have to try your suggestion about stopping the
> service
> and restoring.
> Thanks for the help.
> "JT" wrote:
>|||I normally do set the transaction isolation level to read uncommitted. I di
d
that in this case as well.
I even tried using the hint "with(readuncommitted)" but it was still blocked
by the stuck spid when I tried to return the sysobject rows of tables that
were affected.
Our DBA is going to bounce the service later today. I'm hoping it will
clear up after the restart.
"JT" wrote:

> When querying sysobjects (or any other blocked table), you can get around
> the locks by changing the isolation level to read uncommitted data. Howeve
r,
> this should not be used in a production system except perhaps in some
> reporting situations.
> set transaction isolation level read uncommitted
> select * from sysobjects
> "tthrone" <tthrone@.discussions.microsoft.com> wrote in message
> news:1C4B57D4-1894-4688-8E32-6157E28AD503@.microsoft.com...
>
>

Killed/Rollback process hogging ALL CPU resources.

I have a test database for the end users to test their select queries for reports.
One of my users is writing queries that cause locking in the database. I killed the process last evening and they are in Killed/Rollback status but are still hogging 90% of the CPU resources for the past 12 hrs. I tried killing them several times but no go.

I know that the best way to clear of these processes is by restarting SQL Server. If that is not an option is there is any other way we can clean these processes?

Also the user running these queries has a read only and create view access to the database. From my experience processes that go into Kill/Rollback state after you kill them are processes associated with some update transaction. Since the user as far as i know is running Select commands would an infinite loop cause this ?

thanks
ninaWhat a good time to talk about execute only authorit to stored procedures...

Your rool back can take up 2 twice as long as the original process...maybe longer...

I doubt it was select only...any chance a work table was involved with millions of rows and they did a delete to clear it out?

Guess you don't have the opportunity to do a code review...

If you stop and restart the server, it'll just pick up from where it left off.

What version is this?

Is this a dev or production box?

I know I saw someone once who discussed this...but it was messy

Before you issue a kill, you should find out what the spid was doing...did you do sp_who to see how much I/O and CPU it was using?

Do you monitor the developers with profiler?

What login Id did the developer login with?|||Hello Brett
thanks for responding. This is a development box and that is probably the only good thing about this entire mess.
And no i killed the process without actually looking into the query that it was running. It is SQL Server 2000 box and the user has a SQL Server account and he uses query analyzer to write/test his queries.
The user has create view rights and belongs to db_datareader role for just the one test database on the server.
Would a query running into an infinite loop cause this problem ?
Before killing the process it was using about 70% of CPU but it kept hogging more resources through the night after i killed it and this morning everything on the server came to a standstill as it hogged 99% of CPU|||Put the user in the pillory, until the rollback is complete. They should learn after that ;-)|||The rollback ran through the night and ate up all our server resources and still did not complete. I just went in and restarted the server. Since this is a development environment it was not that much of a problem.

What i would like to know is that was restarting the SQLServer the only option that we have in such a situation ? And also would a select query every cause a rollback ?|||My guess is that if the restart worked then it wasn't rolling back...

Did you check and see if to spids where deadlocked?|||Yes i did check for deadlocks and there were none in the system.|||OK, Try this next time

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

That will throw everyone out without having to issue a kill

btw did you kill all spids?|||Thanks will keep the Alter statement for future reference. And yes i did try to kill all the processes accociated with that user. And since there were only 4-5 processes for that user i know i got them all.

KILLED/ROLLBACK - LCK_M_SCH_M - Estimated rollback completion: 0%

Hi,
Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
#######################################################
spid
--
72
95
kpid
--
512
4784
lastwaittype
--
LCK_M_SCH_M
LCK_M_SCH_M
waitresource
--
TAB: 2:1095871390:0
TAB: 2:1544549701:0
login_time
--
2008-02-01 16:24:49.603
2008-02-01 16:44:10.533
last_batch
--
2008-02-01 16:24:50.590
2008-02-01 16:44:10.797
open_tran
--
1
1
status
--
suspended
suspended
hostname
--
WWW1
WWW2
program_name
--
.Net SqlClient Data Provider
.Net SqlClient Data Provider
cmd
--
KILLED/ROLLBACK (before kill was EXECUTE)
KILLED/ROLLBACK (before kill was EXECUTE)
Kill:
SPID 72: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
SPID 95: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
kill 72 with statusonly and kill 95 with status only say the same
#######################################################
I read archive post on group and mainly people suggest restart sql service.
I have this on production environment, so it isn't so easy to do.
Do you know any better way I think restart should be the last one.
--
Regards,
anxcompanxcomp,
I agree that restarting the service should be the last resort.
Unfortunately, I do not know of another resort.
If the locks still being held do not block anyone, then you can wait to
schedule the restart, but if it is blocking users of the system then you
will need to schedule the restart soon. (Happened to me today on a
development server. Always frustrating, but very much so on a production
server.)
RLF
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
> Hi,
> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
> #######################################################
> spid
> --
> 72
> 95
> kpid
> --
> 512
> 4784
> lastwaittype
> --
> LCK_M_SCH_M
> LCK_M_SCH_M
>
> waitresource
> --
> TAB: 2:1095871390:0
> TAB: 2:1544549701:0
> login_time
> --
> 2008-02-01 16:24:49.603
> 2008-02-01 16:44:10.533
> last_batch
> --
> 2008-02-01 16:24:50.590
> 2008-02-01 16:44:10.797
> open_tran
> --
> 1
> 1
> status
> --
> suspended
> suspended
> hostname
> --
> WWW1
> WWW2
> program_name
> --
> .Net SqlClient Data Provider
> .Net SqlClient Data Provider
> cmd
> --
> KILLED/ROLLBACK (before kill was EXECUTE)
> KILLED/ROLLBACK (before kill was EXECUTE)
> Kill:
> SPID 72: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> SPID 95: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> kill 72 with statusonly and kill 95 with status only say the same
> #######################################################
> I read archive post on group and mainly people suggest restart sql
> service.
> I have this on production environment, so it isn't so easy to do.
> Do you know any better way I think restart should be the last one.
> --
> Regards,
> anxcomp|||Russel
Even if you restart the service, SQL Server attempts to recover the
database and in that case it will take long time.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
> anxcomp,
> I agree that restarting the service should be the last resort.
> Unfortunately, I do not know of another resort.
> If the locks still being held do not block anyone, then you can wait to
> schedule the restart, but if it is blocking users of the system then you
> will need to schedule the restart soon. (Happened to me today on a
> development server. Always frustrating, but very much so on a production
> server.)
> RLF
> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>|||Uri,
I know that can happen, but in actuality I have never had it take more than
a couple of minutes to recover even for a transaction that had been running
a couple of hours. Perhaps this is because rollback in a database that is
not in use yet is a lot quicker than rolling back a busy database.
However, in the case where you cannot kill a process and it is holding
critical locks, is there another choice? If so, I would love to know how to
resolve the problem without restarting the server.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Russel
> Even if you restart the service, SQL Server attempts to recover the
> database and in that case it will take long time.
>
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait to
>> schedule the restart, but if it is blocking users of the system then you
>> will need to schedule the restart soon. (Happened to me today on a
>> development server. Always frustrating, but very much so on a production
>> server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>|||Russel
Yes, I had an epxerience where one person killed the long running
transaction and later on did a restart m and finally database is gone due to
recovering process
You are right , there is only chance to restart the service, I just wanted
to make apoint if you kill a long running transaction (DBCC REINDEX...) be
careful to restart the service and I'm recommeding to wait till rollback
will be completed
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
> Uri,
> I know that can happen, but in actuality I have never had it take more
> than a couple of minutes to recover even for a transaction that had been
> running a couple of hours. Perhaps this is because rollback in a database
> that is not in use yet is a lot quicker than rolling back a busy database.
> However, in the case where you cannot kill a process and it is holding
> critical locks, is there another choice? If so, I would love to know how
> to resolve the problem without restarting the server.
> RLF
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Even if you restart the service, SQL Server attempts to recover the
>> database and in that case it will take long time.
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait to
>> schedule the restart, but if it is blocking users of the system then you
>> will need to schedule the restart soon. (Happened to me today on a
>> development server. Always frustrating, but very much so on a
>> production server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>>
>|||Uri, Thanks for the additional note. - RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%236ff8gMaIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Russel
> Yes, I had an epxerience where one person killed the long running
> transaction and later on did a restart m and finally database is gone due
> to recovering process
> You are right , there is only chance to restart the service, I just wanted
> to make apoint if you kill a long running transaction (DBCC REINDEX...)
> be careful to restart the service and I'm recommeding to wait till
> rollback will be completed
>
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
>> Uri,
>> I know that can happen, but in actuality I have never had it take more
>> than a couple of minutes to recover even for a transaction that had been
>> running a couple of hours. Perhaps this is because rollback in a database
>> that is not in use yet is a lot quicker than rolling back a busy
>> database.
>> However, in the case where you cannot kill a process and it is holding
>> critical locks, is there another choice? If so, I would love to know how
>> to resolve the problem without restarting the server.
>> RLF
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Even if you restart the service, SQL Server attempts to recover the
>> database and in that case it will take long time.
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait to
>> schedule the restart, but if it is blocking users of the system then
>> you will need to schedule the restart soon. (Happened to me today on a
>> development server. Always frustrating, but very much so on a
>> production server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>>
>>
>|||Uri,
One additional comment: In the case described by anxcomp, I am unsure that
the rollback will ever complete. I kept one in that state open for a day
(and a long day it was, too) and it never rolled back and for the whole time
a
KILL spid WITH STATUSONLY:
reported the following:
Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
I have also seen a similar situation, but it reported:
Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
FWIW,
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%236ff8gMaIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Russel
> Yes, I had an epxerience where one person killed the long running
> transaction and later on did a restart m and finally database is gone due
> to recovering process
> You are right , there is only chance to restart the service, I just wanted
> to make apoint if you kill a long running transaction (DBCC REINDEX...)
> be careful to restart the service and I'm recommeding to wait till
> rollback will be completed
>
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
>> Uri,
>> I know that can happen, but in actuality I have never had it take more
>> than a couple of minutes to recover even for a transaction that had been
>> running a couple of hours. Perhaps this is because rollback in a database
>> that is not in use yet is a lot quicker than rolling back a busy
>> database.
>> However, in the case where you cannot kill a process and it is holding
>> critical locks, is there another choice? If so, I would love to know how
>> to resolve the problem without restarting the server.
>> RLF
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Even if you restart the service, SQL Server attempts to recover the
>> database and in that case it will take long time.
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait to
>> schedule the restart, but if it is blocking users of the system then
>> you will need to schedule the restart soon. (Happened to me today on a
>> development server. Always frustrating, but very much so on a
>> production server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>>
>>
>|||>I kept one in that state open for a day (and a long day it was, too) and it
>never rolled back and for the whole time
So what does it do? Is it just killed without rollback the process?
Fortunately or unfortunately :-) I did not happen to use KILL command in a
production/development that is on SQL Server 2005 , moreover I have never
used WITH STATUSONLY oprion
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23tdvtINaIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Uri,
> One additional comment: In the case described by anxcomp, I am unsure that
> the rollback will ever complete. I kept one in that state open for a day
> (and a long day it was, too) and it never rolled back and for the whole
> time a
> KILL spid WITH STATUSONLY:
> reported the following:
> Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
> I have also seen a similar situation, but it reported:
> Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
> FWIW,
> RLF
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%236ff8gMaIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Yes, I had an epxerience where one person killed the long running
>> transaction and later on did a restart m and finally database is gone due
>> to recovering process
>> You are right , there is only chance to restart the service, I just
>> wanted to make apoint if you kill a long running transaction (DBCC
>> REINDEX...) be careful to restart the service and I'm recommeding to wait
>> till rollback will be completed
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
>> Uri,
>> I know that can happen, but in actuality I have never had it take more
>> than a couple of minutes to recover even for a transaction that had been
>> running a couple of hours. Perhaps this is because rollback in a
>> database that is not in use yet is a lot quicker than rolling back a
>> busy database.
>> However, in the case where you cannot kill a process and it is holding
>> critical locks, is there another choice? If so, I would love to know
>> how to resolve the problem without restarting the server.
>> RLF
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Even if you restart the service, SQL Server attempts to recover the
>> database and in that case it will take long time.
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait
>> to schedule the restart, but if it is blocking users of the system
>> then you will need to schedule the restart soon. (Happened to me
>> today on a development server. Always frustrating, but very much so
>> on a production server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>>
>>
>>
>|||Uri,
> So what does it do? Is it just killed without rollback the process?
My window into what is really going on is only the tools that SQL Server
provides. Did it rollback at all? Maybe, but it claims it has not. Did it
really make any significant changes? No idea. Did it hold blocking locks?
Yes, preventing some application functions from working. Et cetera.
It _claims_ that it has entered the rollback state, but has rolled nothing
back. :-(
(I hope that anxcomp is finding this interesting.)
RLF|||I've tried switch database to simple or offline mode for a moment, but
without success.
#############################
alter database db1
set single_user
with
rollback immediate
alter database db1
set offline
with
rollback immediate
#############################
It only remains for me to restart :-(
Hope database restore will not take long time.
--
Regards,
anxcomp|||anxcomp,
When you say "database restore" I assume that you mean the automatic
"database recovery" that happens at startup. I reviewed the logs of the
server I had to do this on. (And, yes, I do hate being pushed into taking
that action.)
The SQL Server did some automatic DBCC CHECKDB and other recovery work. For
the big, active database at the root of the problem at recovery time it
said.
Recovery of database "BigDatabase" (111) is 0% complete (approximately
4826 seconds remain)
That would have been about 80 minutes, but it actually recovered in 29
seconds with 1 transaction rolling back.
For what that is worth,
RLF
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:4EDF93EA-C416-404A-8B20-0D8A982A9944@.microsoft.com...
> I've tried switch database to simple or offline mode for a moment, but
> without success.
> #############################
> alter database db1
> set single_user
> with
> rollback immediate
> alter database db1
> set offline
> with
> rollback immediate
> #############################
> It only remains for me to restart :-(
> Hope database restore will not take long time.
> --
> Regards,
> anxcomp|||Hi,
I've restarted today morning SQL Service. Fortunately SQL started without
any problem (about 30 seconds). ActiveMonitor doesn't show any problematic
processes :-)
--
Regards,
anxcomp