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