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.
> > >>
> > >>
> > >>
> > >>
> > >>
> > >
> > >
>
Showing posts with label estimated. Show all posts
Showing posts with label estimated. Show all posts
Monday, March 26, 2012
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
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
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,
anxcomp
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
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...
>
|||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...
>
|||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...
>
|||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,
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...
>
|||>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...
>
|||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
sql
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
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
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...
>
|||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...
>
|||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...
>
|||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,
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...
>
|||>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...
>
|||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
sql
Friday, March 23, 2012
kill process always shows 0% completion
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?
I'm running :
kill spid
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?
ClemThis post looks familiar...
If you have a large transaction that needs rolling back, it could take a long time.
This KB is a very good reference for the "kill" process. I suggest you take a quick peek.
http://support.microsoft.com/kb/171224sql
Wednesday, March 21, 2012
Kill 142,
Kill 142, I was trying to kill one of the session and found the following
error.
SPID 142: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
when I use : dbcc opentran, it gave me following result
Oldest active transaction:
SPID (server process ID) : 142
UID (user ID) : 1
Name : user_transaction
LSN : (535:511:1)
Start time : May 3 2007 10:31:54:390AM
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
when I used sp_who2, it gave me following entry
142 RUNNABLE WebSp WEBEDA2 . master KILLED/ROLLBACK 16 1 01/01
00:00:00 .Net SqlClient Data Provider 142
when I used sp_lock, it gave me following entries.
63 1 (master) 85575343 0 TAB IS GRANT
142 11 (myDB) 354100302 1 KEY (9500ee50c84e) X GRANT
142 11 (myDB) 354100302 1 PAG 1:2660 IX GRANT
142 11 (myDB) 354100302 0 TAB IX GRANT
142 11 (myDB) 0 0 DB S GRANT
I would appreciate if anyone help me out... its the production box and I
can't restart the SQL Server Services.
Thanks
Rogers,
If you repeatedly do "KILL 142 WITH STATUSONLY" it will probably continually
repeat the same status. If so, then this kind only gets eliminated by
restarting the SQL Server service.
As long as it is not locking anything anyone else needs, it can be ignored,
but it looks like that may not be the case with the MyDB locks.
Time to figure out the nearest window for a restart.
RLF
"Rogers" <naissani@.hotmail.com> wrote in message
news:%23qvet%23njHHA.1624@.TK2MSFTNGP02.phx.gbl...
> Kill 142, I was trying to kill one of the session and found the following
> error.
> SPID 142: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> when I use : dbcc opentran, it gave me following result
> Oldest active transaction:
> SPID (server process ID) : 142
> UID (user ID) : 1
> Name : user_transaction
> LSN : (535:511:1)
> Start time : May 3 2007 10:31:54:390AM
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> when I used sp_who2, it gave me following entry
> 142 RUNNABLE WebSp WEBEDA2 . master KILLED/ROLLBACK 16 1 01/01
> 00:00:00 .Net SqlClient Data Provider 142
> when I used sp_lock, it gave me following entries.
> 63 1 (master) 85575343 0 TAB IS GRANT
> 142 11 (myDB) 354100302 1 KEY (9500ee50c84e) X GRANT
> 142 11 (myDB) 354100302 1 PAG 1:2660 IX GRANT
> 142 11 (myDB) 354100302 0 TAB IX GRANT
> 142 11 (myDB) 0 0 DB S GRANT
> I would appreciate if anyone help me out... its the production box and I
> can't restart the SQL Server Services.
> Thanks
>
error.
SPID 142: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
when I use : dbcc opentran, it gave me following result
Oldest active transaction:
SPID (server process ID) : 142
UID (user ID) : 1
Name : user_transaction
LSN : (535:511:1)
Start time : May 3 2007 10:31:54:390AM
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
when I used sp_who2, it gave me following entry
142 RUNNABLE WebSp WEBEDA2 . master KILLED/ROLLBACK 16 1 01/01
00:00:00 .Net SqlClient Data Provider 142
when I used sp_lock, it gave me following entries.
63 1 (master) 85575343 0 TAB IS GRANT
142 11 (myDB) 354100302 1 KEY (9500ee50c84e) X GRANT
142 11 (myDB) 354100302 1 PAG 1:2660 IX GRANT
142 11 (myDB) 354100302 0 TAB IX GRANT
142 11 (myDB) 0 0 DB S GRANT
I would appreciate if anyone help me out... its the production box and I
can't restart the SQL Server Services.
Thanks
Rogers,
If you repeatedly do "KILL 142 WITH STATUSONLY" it will probably continually
repeat the same status. If so, then this kind only gets eliminated by
restarting the SQL Server service.
As long as it is not locking anything anyone else needs, it can be ignored,
but it looks like that may not be the case with the MyDB locks.
Time to figure out the nearest window for a restart.
RLF
"Rogers" <naissani@.hotmail.com> wrote in message
news:%23qvet%23njHHA.1624@.TK2MSFTNGP02.phx.gbl...
> Kill 142, I was trying to kill one of the session and found the following
> error.
> SPID 142: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> when I use : dbcc opentran, it gave me following result
> Oldest active transaction:
> SPID (server process ID) : 142
> UID (user ID) : 1
> Name : user_transaction
> LSN : (535:511:1)
> Start time : May 3 2007 10:31:54:390AM
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> when I used sp_who2, it gave me following entry
> 142 RUNNABLE WebSp WEBEDA2 . master KILLED/ROLLBACK 16 1 01/01
> 00:00:00 .Net SqlClient Data Provider 142
> when I used sp_lock, it gave me following entries.
> 63 1 (master) 85575343 0 TAB IS GRANT
> 142 11 (myDB) 354100302 1 KEY (9500ee50c84e) X GRANT
> 142 11 (myDB) 354100302 1 PAG 1:2660 IX GRANT
> 142 11 (myDB) 354100302 0 TAB IX GRANT
> 142 11 (myDB) 0 0 DB S GRANT
> I would appreciate if anyone help me out... its the production box and I
> can't restart the SQL Server Services.
> Thanks
>
Subscribe to:
Posts (Atom)