KDC Problem: running

Showing posts with label running. Show all posts

Monday, March 26, 2012

Killing a User Process

Hi All
Here is the scenario, a user has a query running which has
taken far too long and needs to be killed. This is a front
end and the user does not have access to SQL Server.
1. Can that user Kill the Process.
2. What wuld be the code to do that?
Many Thanks
PaulHi Paul,
1. Most likely not. To kill a SQL Server process you have to be a member of
the sysadmin or processadmin roles, and ordinary users usually aren't.
2. KILL <spid> where spid is the process is of the process.
Note that KILLing a process will cause all the transactions that have been
started by the process to be rolled back, so it can take some time before
the process is actually really killed.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"paul Blackler" <pblackler@.themis.co.uk> wrote in message
news:06ae01c37081$75949630$a601280a@.phx.gbl...
> Hi All
> Here is the scenario, a user has a query running which has
> taken far too long and needs to be killed. This is a front
> end and the user does not have access to SQL Server.
> 1. Can that user Kill the Process.
> 2. What wuld be the code to do that?
> Many Thanks
> Paul

killing a process shows 0% completion time

Hello,
I'm running Sql server 2005 and I've noticed that when I kill a
process, it always shows
"Estimated rollback completion: 0%. Estimated time remaining: 0
seconds."
Even though the process does kill successfully, these numbers never
change. Is there some setting I have to change or what?
Can anyone help?
Thanks.When you kill a running process/transaction, the system will have to go
through and rollback the transaction. There is nothing you can do here other
than to wait for completion.
If you force a system restart, the transaction will be re-rollbacked on the
next restart.
--
-oj
<clemlau@.yahoo.com> wrote in message
news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
> Hello,
> I'm running Sql server 2005 and I've noticed that when I kill a
> process, it always shows
>
> "Estimated rollback completion: 0%. Estimated time remaining: 0
> seconds."
>
> Even though the process does kill successfully, these numbers never
> change. Is there some setting I have to change or what?
> Can anyone help?
> Thanks.
>|||This is a multi-part message in MIME format.
--010208050704050705070309
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
How are you getting the completion figure? Are you using "KILL <spid>
WITH STATUSONLY"?
--
*mike hodgson*
http://sqlnerd.blogspot.com
clemlau@.yahoo.com wrote:
>Hello,
>I'm running Sql server 2005 and I've noticed that when I kill a
>process, it always shows
>
>"Estimated rollback completion: 0%. Estimated time remaining: 0
>seconds."
>
>Even though the process does kill successfully, these numbers never
>change. Is there some setting I have to change or what?
>Can anyone help?
>Thanks.
>
>
--010208050704050705070309
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>How are you getting the completion figure? Are you using "KILL
<spid> WITH STATUSONLY"?</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
<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?] 
 
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. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
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?] 
> 
> 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. 
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> -- 
> mike
> hodgson 
> <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>
> 
> </div>
> 
> 
> oj wrote:
> <blockquote cite="mid%23c$BJRomGHA.2316@.TK2MSFTNGP04.phx.gbl"
> type="cite">
> <pre wrap="">that's your problem. you're not actually killing the spid.
> "WITH STATUSONLY
> Generates a progress report on a given spid or UOW that is being rolled
> back due to an earlier KILL statement. KILL WITH STATUSONLY does not
> terminate or roll back the spid or UOW, it only displays the current
> progress of the roll back."
>
> </pre>
> </blockquote>
> </body>
> </html>
> --060106070200070902040203--|||<clemlau@.yahoo.com> wrote in message
news:1151467451.774578.30370@.i40g2000cwc.googlegroups.com...
>
> I'm running kill spid with statusonly. This result always shows 0%
> completion. Whether it takes 5 seconds or 8 hours to rollback, I
> always see 0% completion. (I had a process today that I had to kill
> after running for 6 hours and it took 8 hours to kill but I had no idea
> about it's progress.)
> In sql 2000, this worked everytime I killed a process. I could see the
> % changing and the estimated time to complete changing.
Somethings just don't roll back nicely.
Especially anything calling an XP procedure or cross-database calls.
I've also seen a few cases (with SQL2000) where if the client disconnects
non-cleanly, the rollback may show 100% complete, but the SPID never goes
away.
> Any ideas?
>
> Thanks,
> Clem
>
> Geoff N. Hiten wrote:
> > You are correct.
> >
> > Good catch. Thanks,
> >
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> >
> >
> > "oj" <nospam_ojngo@.home.com> wrote in message
> > news:%23lxophkmGHA.4700@.TK2MSFTNGP05.phx.gbl...
> > > it's sql2k5! ;-)
> > >
> > > the db should be avail as soons as redo is done.
> > >
> > > --
> > > -oj
> > >
> > >
> > >
> > > "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> > > news:e2c80dkmGHA.4052@.TK2MSFTNGP05.phx.gbl...
> > >> And just to add to the doom and gloom, the rollback is part of the
> > >> database recovery. No connections to the database will be allowed
until
> > >> the rollback is complete. I have seen (and survived with job intact)
a
> > >> four-hour unwind on a restart, so this can get very bad.
> > >>
> > >> --
> > >> Geoff N. Hiten
> > >> Senior Database Administrator
> > >> Microsoft SQL Server MVP
> > >>
> > >>
> > >>
> > >> "oj" <nospam_ojngo@.home.com> wrote in message
> > >> news:Ou4O0PkmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> > >> When you kill a running process/transaction, the system will have to
go
> > >> through and rollback the transaction. There is nothing you can do
here
> > >> other than to wait for completion.
> > >>
> > >> If you force a system restart, the transaction will be re-rollbacked
on
> > >> the next restart.
> > >>
> > >> --
> > >> -oj
> > >>
> > >>
> > >>
> > >> <clemlau@.yahoo.com> wrote in message
> > >> news:1151442931.224581.203680@.b68g2000cwa.googlegroups.com...
> > >> Hello,
> > >>
> > >> I'm running Sql server 2005 and I've noticed that when I kill a
> > >> process, it always shows
> > >>
> > >>
> > >> "Estimated rollback completion: 0%. Estimated time remaining: 0
> > >> seconds."
> > >>
> > >>
> > >> Even though the process does kill successfully, these numbers
never
> > >> change. Is there some setting I have to change or what?
> > >>
> > >> Can anyone help?
> > >>
> > >> Thanks.
> > >>
> > >>
> > >>
> > >>
> > >>
> > >
> > >
>

killing a process shows 0% completion time

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

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

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

Killer problem with Scheduled Reports

I'm running on SQL 2000 with SRS. Standard install (I think, it was all set up by IT)

I have reports that run fine in the Report Manager but when they are run through a schedule (email) some of them (albeit most) "rsProcessingAborted" because of a report error.

Looking at the logs it seems generally based around comparison failure because of datatypes, eg (from the logs):

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: The processing of sort expression for the list 'List1' cannot be performed. The comparison failed. Please check the data type returned by sort expression. Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: The processing of group expression for the table 'tblGroupTable' cannot be performed. The comparison failed. Please check the data type returned by group expression.

Now, when I remove sorting/grouping the reports run through the schedule OK; but that is obviously NOT what I want the report to look like. But it proves that the sorting/grouping has something to do with the problem.

I'm pretty sure that they are NOT the problem though, as the reports render in Report Manager fine with all the original sorting/grouping in place (ie no problems during development, just when we wanted them served to the employees. Isn't that always the way it goes?). We've even had some reports which scheduled OK previously, but throw errors now.

I'm at a loss!!

Could it be a time-out issue somewhere? If so: where? Looking at the Execution Logs, the reports that fail have unusually long Process Times, with a Data Retrieval Time of 0. I guess that the process time is longer because of processing the error, but does the Data Retrieval time of 0 mean that no data was delivered to the report? How can that be? IT tell me it can't be memory issues as it's sitting on a 2GB machine, but could there be something else with SQL set-up? I googled the error and found a few pages where people said that puting SRS in its own application pool fixed their problem. When I passed this on to IT they said that would only help memory... Any ideas?

Any suggestions greatfully appreciated

Do the report subscriptions ALWAYS fail, or intermittently?

Do those same reports ALWAYS succeed when executed live?

How much RAM is on the Dev machine, and what OS?

What OS is on the productio machine?

What changed between the time that some scheduled reports worked fine, and when they started erroring?

Removing the grouping/sorting may have alleviated the load on the processing engine enough so that the report succeeded.

|||Also, what are the types and #'s of CPU's on the Dev and Production machines?|||

Mike Schetterer -- MSFT wrote:
Do the report subscriptions ALWAYS fail, or intermittently?

The reports seem to fail always when they contain some data. Maybe the oposite is better: The reports don't fail when they do not contain data. Of the report I can remember off the top of my head: the schedule didn't fail on 4 occasions, three of those didn't have any data, one had one record.

For example, for one of our reports that is meant to run this morning:

Time Start

Time End

Data Retrieval

Process

Render

Status

User Name

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=3

6/02/2006 7:00:08 AM

6/02/2006 7:00:09 AM

112

rsProcessingAborted

NT AUTHORITY\NETWORK SERVICE

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=8

6/02/2006 7:00:08 AM

6/02/2006 7:00:12 AM

2979

359

255

rsSuccess

NT AUTHORITY\NETWORK SERVICE

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=6

6/02/2006 7:00:57 AM

6/02/2006 7:00:58 AM

rsProcessingAborted

NT AUTHORITY\NETWORK SERVICE

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=11

6/02/2006 7:00:57 AM

6/02/2006 7:00:58 AM

rsProcessingAborted

NT AUTHORITY\NETWORK SERVICE

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=13

6/02/2006 7:00:58 AM

rsProcessingAborted

NT AUTHORITY\NETWORK SERVICE

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=1

6/02/2006 7:00:58 AM

rsProcessingAborted

NT AUTHORITY\NETWORK SERVICE

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=9

6/02/2006 7:00:59 AM

rsProcessingAborted

NT AUTHORITY\NETWORK SERVICE

pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=10

6/02/2006 7:00:59 AM

rsProcessingAborted

NT AUTHORITY\NETWORK SERVICE

Mike Schetterer -- MSFT wrote:
Do those same reports ALWAYS succeed when executed live?

YES!

Mike Schetterer -- MSFT wrote:
How much RAM is on the Dev machine, and what OS?

Intel Xeon 2.4Gb x 2, 2Gb Ram

The Dev machine and the production machine are the same. I use a Remote Desktop into the production machine from my desktop, I guess the documents are stored on my workstation and deployed to the production machine.

Mike Schetterer -- MSFT wrote:
What OS is on the productio machine?

Windows Server 2003, SP1

Mike Schetterer -- MSFT wrote:
What changed between the time that some scheduled reports worked fine, and when they started erroring?

Nothing that I am aware of... but I'll ask the IT people...

Mike Schetterer -- MSFT wrote:
Removing the grouping/sorting may have alleviated the load on the processing engine enough so that the report succeeded.

The schedules fail regardless of the time of day they run (I wondered if the server got overloaded whilst trying to do other things): but there is no difference.

Another interesting fact: whilst investigating I discovered that one of the reports that ran through a schedule after all grouping and sorting was removed displayed "#error" in a cell that contained the result from a Library I'd written. Though this report runs and displays fine through the Report Manager! (this field was not used in the grouping or sorting either).

Yes, I've read somewhere else that how you've configured the Application Pool can make a difference (our IT said it wouldn't here). But I've definitely read in a different forum that that fixed their problem.

At the moment the Report Server Interface and the Report Server are in the DefaultAppPool.

Thanks for your interest!

Perry

|||

Do your reports have parameters?

When you create your subscription - are the parameters set staticly or are the based on a query. If they're query based - can you try running the query and selecting some of the value combinations you receive in return through the UI to see if they work?

Your comment above about report executions failing when there is data, but succeeding when there is no data - is it true to say that whenever there is data the subscription fails, or just some of the time?

Thanks,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Lukasz Pawlowski -- MS wrote:
Do your reports have parameters?

Some do, others don't, but it doesn't seem to be a problem.

Lukasz Pawlowski -- MS wrote:
When you create your subscription - are the parameters set staticly or are the based on a query. If they're query based - can you try running the query and selecting some of the value combinations you receive in return through the UI to see if they work?

Both, or sometimes all three (included type in)

Yes, ALL the reports run fine through the Report Manager.

Lukasz Pawlowski -- MS wrote:
Your comment above about report executions failing when there is data, but succeeding when there is no data - is it true to say that whenever there is data the subscription fails, or just some of the time?

No: they fail most of the time. In the section of the ExecutionLog in a previous post you can see that for that report it ran once (with data) and failed the rest (they would have had data too.

Thanks,

Perry

|||

What are the group and sort expressions from the report that you find is failing?

-Lukasz

|||

Lukasz Pawlowski -- MS wrote:
What are the group and sort expressions from the report that you find is failing?

In every case it's "=Fields!FieldName.value"

Perry

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?

Thanks,
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

Kill process

If I have a process running and I want to kill it, and I right click on the
process, and select "kill process", why does it not get killed? How do I
kill it?
--
Message posted via http://www.sqlmonster.comin Query Analyzer:
KILL XX
Where XX is the Spid
Greg Jackson
PDX, Oregon|||On Tue, 08 Feb 2005 19:38:46 GMT, Robert Richards via SQLMonster.com
wrote:
>If I have a process running and I want to kill it, and I right click on the
>process, and select "kill process", why does it not get killed? How do I
>kill it?
Hi Robert,
If you do this frome Enterprise Manager, then don't forget to refresh the
view.
Also, if the process has been changing data, all changes have to be rolled
back. This can take some time (rolling back is often slower than making
the changes)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||You can use the following to see the status; the process may be in rollback:
KILL (spid number) WITH STATUSONLY
"Robert Richards via SQLMonster.com" wrote:
> If I have a process running and I want to kill it, and I right click on the
> process, and select "kill process", why does it not get killed? How do I
> kill it?
> --
> Message posted via http://www.sqlmonster.com
>

Kill process

If I have a process running and I want to kill it, and I right click on the
process, and select "kill process", why does it not get killed? How do I
kill it?
Message posted via http://www.sqlmonster.com
in Query Analyzer:
KILL XX
Where XX is the Spid
Greg Jackson
PDX, Oregon
|||On Tue, 08 Feb 2005 19:38:46 GMT, Robert Richards via SQLMonster.com
wrote:

>If I have a process running and I want to kill it, and I right click on the
>process, and select "kill process", why does it not get killed? How do I
>kill it?
Hi Robert,
If you do this frome Enterprise Manager, then don't forget to refresh the
view.
Also, if the process has been changing data, all changes have to be rolled
back. This can take some time (rolling back is often slower than making
the changes)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||You can use the following to see the status; the process may be in rollback:
KILL (spid number) WITH STATUSONLY
"Robert Richards via SQLMonster.com" wrote:

> If I have a process running and I want to kill it, and I right click on the
> process, and select "kill process", why does it not get killed? How do I
> kill it?
> --
> Message posted via http://www.sqlmonster.com
>

Kill process

If I have a process running and I want to kill it, and I right click on the
process, and select "kill process", why does it not get killed? How do I
kill it?
Message posted via http://www.droptable.comin Query Analyzer:
KILL XX
Where XX is the Spid
Greg Jackson
PDX, Oregon|||On Tue, 08 Feb 2005 19:38:46 GMT, Robert Richards via droptable.com
wrote:

>If I have a process running and I want to kill it, and I right click on the
>process, and select "kill process", why does it not get killed? How do I
>kill it?
Hi Robert,
If you do this frome Enterprise Manager, then don't forget to refresh the
view.
Also, if the process has been changing data, all changes have to be rolled
back. This can take some time (rolling back is often slower than making
the changes)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||You can use the following to see the status; the process may be in rollback:
KILL (spid number) WITH STATUSONLY
"Robert Richards via droptable.com" wrote:

> If I have a process running and I want to kill it, and I right click on th
e
> process, and select "kill process", why does it not get killed? How do I
> kill it?
> --
> Message posted via http://www.droptable.com
>sql

kill long running query

I am changing some field types in a large table
by creating the new table and then selecting the
data into it. So no changes are being made to the
existing data.
I started this at 5:00 last night, and by 11:00
it became apparent that it wasn't going to finish
until this weekend.
I can run queries in UNCOMMITTED READ isolation level
and see how many images have been copied. By 11:00
it was only 12% done.
So I cancelled the query at 11:00. 12 hours later,
it is about half way done rolling back. As a result
we can't access the table and get any work done today.
I don't need this transaction rolled back. I could
just drop the temporary table and be done with it.
I've looked at the KILL copmmand, but that just causes
it to rollback, so it doesn't help.
I could reboot the server, but as far as I know, it might
pick up where it left off and continue rolling back the
transaction.
Is there any way out of this hole that I've dug myself into?
Thanks,
Brad.> So I cancelled the query at 11:00. 12 hours later,
> it is about half way done rolling back. As a result
> we can't access the table and get any work done today.
If it is rolling back a SELECT INTO a new table, why would this prevent you
from accessing the existing table? Or did you mean to explain that the
rollback is consuming the server in terms of resources?|||Aaron Bertrand [SQL Server MVP] wrote :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
I don't know the answer to that.
I assume that it has affected so many rows
(I was copying the entire table to a new table)
that the locks have been promoted to table level.
2G rows.
The script I was using use ISOLATION LEVEL SERIALIZABLE.
The question has been rendered moot, as the server crashed.
Thanks,
Brad.|||Aaron Bertrand [SQL Server MVP] wrote on 4/4/2008 :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
No, I don't know why it would be adding locks
on the SOURCE table.
What led me to think that was that queries against
any other table would return reasonably, while
queries against either of those tables would run
up to 30 minutes without returning before I killed them.
Thanks,
Brad.|||2 billion rows, yeah that's going to be tough. If you have the space, I
would suggest making a backup of the database, restoring it as a different
database, and then copy the rows from the restored copy. Then you can use a
much lower isolation level because you don't have to worry about people
coming in and changing the source rows while you are copying...
"Brad White" <bwhite_at_inebraska_dot_com@.remove.nul> wrote in message
news:mn.24b57d840b74b3fd.81110@.remove.nul...
> Aaron Bertrand [SQL Server MVP] wrote :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
>> If it is rolling back a SELECT INTO a new table, why would this prevent
>> you from accessing the existing table? Or did you mean to explain that
>> the rollback is consuming the server in terms of resources?
> I don't know the answer to that.
> I assume that it has affected so many rows
> (I was copying the entire table to a new table)
> that the locks have been promoted to table level.
> 2G rows.
> The script I was using use ISOLATION LEVEL SERIALIZABLE.
> The question has been rendered moot, as the server crashed.
> Thanks,
> Brad.
>

kill long running query

I am changing some field types in a large table
by creating the new table and then selecting the
data into it. So no changes are being made to the
existing data.
I started this at 5:00 last night, and by 11:00
it became apparent that it wasn't going to finish
until this weekend.
I can run queries in UNCOMMITTED READ isolation level
and see how many images have been copied. By 11:00
it was only 12% done.
So I cancelled the query at 11:00. 12 hours later,
it is about half way done rolling back. As a result
we can't access the table and get any work done today.
I don't need this transaction rolled back. I could
just drop the temporary table and be done with it.
I've looked at the KILL copmmand, but that just causes
it to rollback, so it doesn't help.
I could reboot the server, but as far as I know, it might
pick up where it left off and continue rolling back the
transaction.
Is there any way out of this hole that I've dug myself into?
Thanks,
Brad.
> So I cancelled the query at 11:00. 12 hours later,
> it is about half way done rolling back. As a result
> we can't access the table and get any work done today.
If it is rolling back a SELECT INTO a new table, why would this prevent you
from accessing the existing table? Or did you mean to explain that the
rollback is consuming the server in terms of resources?
|||Aaron Bertrand [SQL Server MVP] wrote :
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
I don't know the answer to that.
I assume that it has affected so many rows
(I was copying the entire table to a new table)
that the locks have been promoted to table level.
2G rows.
The script I was using use ISOLATION LEVEL SERIALIZABLE.
The question has been rendered moot, as the server crashed.
Thanks,
Brad.
|||Aaron Bertrand [SQL Server MVP] wrote on 4/4/2008 :
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
No, I don't know why it would be adding locks
on the SOURCE table.
What led me to think that was that queries against
any other table would return reasonably, while
queries against either of those tables would run
up to 30 minutes without returning before I killed them.
Thanks,
Brad.
|||2 billion rows, yeah that's going to be tough. If you have the space, I
would suggest making a backup of the database, restoring it as a different
database, and then copy the rows from the restored copy. Then you can use a
much lower isolation level because you don't have to worry about people
coming in and changing the source rows while you are copying...
"Brad White" <bwhite_at_inebraska_dot_com@.remove.nul> wrote in message
news:mn.24b57d840b74b3fd.81110@.remove.nul...
> Aaron Bertrand [SQL Server MVP] wrote :
> I don't know the answer to that.
> I assume that it has affected so many rows
> (I was copying the entire table to a new table)
> that the locks have been promoted to table level.
> 2G rows.
> The script I was using use ISOLATION LEVEL SERIALIZABLE.
> The question has been rendered moot, as the server crashed.
> Thanks,
> Brad.
>

kill long running (hung) SQL task

We have an SQL task that occasionally hangs. Is there a function to end a
task if it has been running for a determined period of time? This feature is
available in windows task scheduler, not SQL 2005?
D.
You can write a script to check for long running jobs and
schedule to run at whatever time interval makes sense for
your purposes.
-Sue
On Wed, 7 Feb 2007 08:16:00 -0800, dsm4898
<dsm4898@.discussions.microsoft.com> wrote:

>We have an SQL task that occasionally hangs. Is there a function to end a
>task if it has been running for a determined period of time? This feature is
>available in windows task scheduler, not SQL 2005?

Wednesday, March 21, 2012

Kill command don't work

I've this problem:
A process (SPID 62) with some locks on some tables. The application which
had generated the query are not running, is closed! In SQL server I can see
the SPI 62 that are blocking other users.
I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
I need to restare sql server to solve my problem.
The query that SPID62 are running is a normal INSERT INTO without any
problems or other...
How may I do to obtain more information around the fact that the SQL KILL
command not work?
I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
: "Extimated... rollbak 0%,... 0 minutes"
But the process rest in rollback and not stop.
and why SQL server if the client go off not stop this process itself?
thanks in advance.
TeoI has this case at a customer site just yesterday. It turned out that the table they inserted into
had a trigger which executed an extended stored procedure. If any external (to SQL Server) code
becomes hung, you cannot kill that SPID.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Teo(I)" <TeoI@.discussions.microsoft.com> wrote in message
news:73DE3473-DFAC-4C77-A645-057871F3DDEC@.microsoft.com...
> I've this problem:
> A process (SPID 62) with some locks on some tables. The application which
> had generated the query are not running, is closed! In SQL server I can see
> the SPI 62 that are blocking other users.
> I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
> I need to restare sql server to solve my problem.
> The query that SPID62 are running is a normal INSERT INTO without any
> problems or other...
> How may I do to obtain more information around the fact that the SQL KILL
> command not work?
> I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
> : "Extimated... rollbak 0%,... 0 minutes"
> But the process rest in rollback and not stop.
> and why SQL server if the client go off not stop this process itself?
> thanks in advance.
> Teo|||DBCC traceon 3604 on SPID 62:
Process id 62 killed by hostname SERVER01, host process ID 6816...
but this isn't true! the spid is alive..!!!
If I retry i obtain:
Process id 62 killed by hostname SERVER01, host process ID 6816...
I don't understand!|||well... I've a trigger too... But my trigger do another insert in a second
table (backup table) on a different db in the same istance. In this case a
rollback or kill must work.

Kill command don't work

I've this problem:
A process (SPID 62) with some locks on some tables. The application which
had generated the query are not running, is closed! In SQL server I can see
the SPI 62 that are blocking other users.
I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
I need to restare sql server to solve my problem.
The query that SPID62 are running is a normal INSERT INTO without any
problems or other...
How may I do to obtain more information around the fact that the SQL KILL
command not work?
I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
: "Extimated... rollbak 0%,... 0 minutes"
But the process rest in rollback and not stop.
and why SQL server if the client go off not stop this process itself?
thanks in advance.
Teo
I has this case at a customer site just yesterday. It turned out that the table they inserted into
had a trigger which executed an extended stored procedure. If any external (to SQL Server) code
becomes hung, you cannot kill that SPID.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Teo(I)" <TeoI@.discussions.microsoft.com> wrote in message
news:73DE3473-DFAC-4C77-A645-057871F3DDEC@.microsoft.com...
> I've this problem:
> A process (SPID 62) with some locks on some tables. The application which
> had generated the query are not running, is closed! In SQL server I can see
> the SPI 62 that are blocking other users.
> I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
> I need to restare sql server to solve my problem.
> The query that SPID62 are running is a normal INSERT INTO without any
> problems or other...
> How may I do to obtain more information around the fact that the SQL KILL
> command not work?
> I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
> : "Extimated... rollbak 0%,... 0 minutes"
> But the process rest in rollback and not stop.
> and why SQL server if the client go off not stop this process itself?
> thanks in advance.
> Teo
|||DBCC traceon 3604 on SPID 62:
Process id 62 killed by hostname SERVER01, host process ID 6816...
but this isn't true! the spid is alive..!!!
If I retry i obtain:
Process id 62 killed by hostname SERVER01, host process ID 6816...
I don't understand!
|||well... I've a trigger too... But my trigger do another insert in a second
table (backup table) on a different db in the same istance. In this case a
rollback or kill must work.

Kill command don't work

I've this problem:
A process (SPID 62) with some locks on some tables. The application which
had generated the query are not running, is closed! In SQL server I can see
the SPI 62 that are blocking other users.
I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
I need to restare sql server to solve my problem.
The query that SPID62 are running is a normal INSERT INTO without any
problems or other...
How may I do to obtain more information around the fact that the SQL KILL
command not work?
I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
: "Extimated... rollbak 0%,... 0 minutes"
But the process rest in rollback and not stop.
and why SQL server if the client go off not stop this process itself?
thanks in advance.
TeoI has this case at a customer site just yesterday. It turned out that the ta
ble they inserted into
had a trigger which executed an extended stored procedure. If any external (
to SQL Server) code
becomes hung, you cannot kill that SPID.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Teo(I)" <TeoI@.discussions.microsoft.com> wrote in message
news:73DE3473-DFAC-4C77-A645-057871F3DDEC@.microsoft.com...
> I've this problem:
> A process (SPID 62) with some locks on some tables. The application which
> had generated the query are not running, is closed! In SQL server I can se
e
> the SPI 62 that are blocking other users.
> I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
> I need to restare sql server to solve my problem.
> The query that SPID62 are running is a normal INSERT INTO without any
> problems or other...
> How may I do to obtain more information around the fact that the SQL KILL
> command not work?
> I've already tried with KILL 62 WITH: Kill 62 with status only and I obtai
n
> : "Extimated... rollbak 0%,... 0 minutes"
> But the process rest in rollback and not stop.
> and why SQL server if the client go off not stop this process itself?
> thanks in advance.
> Teo|||DBCC traceon 3604 on SPID 62:
Process id 62 killed by hostname SERVER01, host process ID 6816...
but this isn't true! the spid is alive..!!!
If I retry i obtain:
Process id 62 killed by hostname SERVER01, host process ID 6816...
I don't understand!|||well... I've a trigger too... But my trigger do another insert in a second
table (backup table) on a different db in the same istance. In this case a
rollback or kill must work.sql

Kicking off a job on another SQL server

I have a job that runs daily that creates a backup and copies that backup
off to a second server. I currently have another job running on the second
server that restores that database locally on that server. I gave the job
on server 2 enough time where job 1 on server one will have enough time to
finish before job 2 starts. That being said, sometimes, job 1 runs kind of
close. I am not sure if this is possible, but I would like to create a step
in job 1 that would kick off job 2 on server 2. Any ideas if this can be
done?
AaronHi,
There is a command in Windows Resource Kit "RCMD" , which is used to execute
jobs / executables / batch files remotely.
Thanks
Hari
MCDBA
"Aaron" <amhigley@.hotmail.com> wrote in message
news:#05tLnRwDHA.1908@.TK2MSFTNGP10.phx.gbl...
> I have a job that runs daily that creates a backup and copies that backup
> off to a second server. I currently have another job running on the
second
> server that restores that database locally on that server. I gave the job
> on server 2 enough time where job 1 on server one will have enough time to
> finish before job 2 starts. That being said, sometimes, job 1 runs kind
of
> close. I am not sure if this is possible, but I would like to create a
step
> in job 1 that would kick off job 2 on server 2. Any ideas if this can be
> done?
> Aaron
>|||If you create a linked server, you could
EXEC linkedServerName.msdb..sp_start_job 'job_name'
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Aaron" <amhigley@.hotmail.com> wrote in message
news:#05tLnRwDHA.1908@.TK2MSFTNGP10.phx.gbl...
> I have a job that runs daily that creates a backup and copies that backup
> off to a second server. I currently have another job running on the
second
> server that restores that database locally on that server. I gave the job
> on server 2 enough time where job 1 on server one will have enough time to
> finish before job 2 starts. That being said, sometimes, job 1 runs kind
of
> close. I am not sure if this is possible, but I would like to create a
step
> in job 1 that would kick off job 2 on server 2. Any ideas if this can be
> done?
> Aaron
>

Monday, March 19, 2012

Keys out of order on page error

We have found we have corruption in our db.
After running DBCC CHECKDB the following error were displayed on
multiple pages.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1285579618, Index ID 3. Keys out of order on page
(1:384662), slots 75 and 76.
I read a post that suggested running DBCC CHECKDB with the repair_build
option.
Since we are a 24/7 shop I cannot restore from tape, because it would
take several hours.
What are the risks of running DBCC CHECKDB with the repair_build option,
and will this fix the problem properly?
We are still unsure what has caused this corruption, any body else have
this problem?
TIA,
-BenThere are a couple of things.
Firstly it is very intensive so turn off as many services
you can get away with, i.e SQL Agent
Secondly depending upon the severity of the error it may
result in data loss. Personally I would try firstly with
REPAIR_FAST, then REPAIR_REBUILD and if all else fails
REPAIR_ALLOW_DATA_LOSS, its that option where you may lose
data.
If the REPAIR_ALLOW_DATA_LOSS doesn't work then you will
have to restore from backup.
As for the reason why to difficult to say.
Good luck
Peter
>--Original Message--
>We have found we have corruption in our db.
>After running DBCC CHECKDB the following error were
displayed on
>multiple pages.
>Server: Msg 2511, Level 16, State 1, Line 1
>Table error: Object ID 1285579618, Index ID 3. Keys out
of order on page
>(1:384662), slots 75 and 76.
>I read a post that suggested running DBCC CHECKDB with
the repair_build
>option.
>Since we are a 24/7 shop I cannot restore from tape,
because it would
>take several hours.
>What are the risks of running DBCC CHECKDB with the
repair_build option,
>and will this fix the problem properly?
>We are still unsure what has caused this corruption, any
body else have
>this problem?
>TIA,
>-Ben
>.
>|||Hi Ben,
As the error is in a non-clustered index, you can just rebuild the index
with DBCC DBREINDEX (<table_name>, <index_name>). Or if you want to be on
the safe side, just drop and recreate the index.
After that, investigate why the corruption occurred in the first place. You
might have hardware problems.
--
Jacco Schalkwijk
SQL Server MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:OJgEzgufEHA.636@.TK2MSFTNGP12.phx.gbl...
> We have found we have corruption in our db.
> After running DBCC CHECKDB the following error were displayed on multiple
> pages.
> Server: Msg 2511, Level 16, State 1, Line 1
> Table error: Object ID 1285579618, Index ID 3. Keys out of order on page
> (1:384662), slots 75 and 76.
> I read a post that suggested running DBCC CHECKDB with the repair_build
> option.
> Since we are a 24/7 shop I cannot restore from tape, because it would take
> several hours.
> What are the risks of running DBCC CHECKDB with the repair_build option,
> and will this fix the problem properly?
> We are still unsure what has caused this corruption, any body else have
> this problem?
> TIA,
> -Ben|||In addition:
The latest update of Books Online has some very detailed recommendations for most error numbers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:374a01c47eee$f6a6a890$a501280a@.phx.gbl...
> There are a couple of things.
> Firstly it is very intensive so turn off as many services
> you can get away with, i.e SQL Agent
> Secondly depending upon the severity of the error it may
> result in data loss. Personally I would try firstly with
> REPAIR_FAST, then REPAIR_REBUILD and if all else fails
> REPAIR_ALLOW_DATA_LOSS, its that option where you may lose
> data.
> If the REPAIR_ALLOW_DATA_LOSS doesn't work then you will
> have to restore from backup.
> As for the reason why to difficult to say.
> Good luck
> Peter
>
> >--Original Message--
> >We have found we have corruption in our db.
> >
> >After running DBCC CHECKDB the following error were
> displayed on
> >multiple pages.
> >
> >Server: Msg 2511, Level 16, State 1, Line 1
> >Table error: Object ID 1285579618, Index ID 3. Keys out
> of order on page
> >(1:384662), slots 75 and 76.
> >
> >I read a post that suggested running DBCC CHECKDB with
> the repair_build
> >option.
> >
> >Since we are a 24/7 shop I cannot restore from tape,
> because it would
> >take several hours.
> >
> >What are the risks of running DBCC CHECKDB with the
> repair_build option,
> >and will this fix the problem properly?
> >
> >We are still unsure what has caused this corruption, any
> body else have
> >this problem?
> >
> >TIA,
> >
> >-Ben
> >.
> >|||I have dropped and recreated the index, thanks. I have also opened up
an issue with our vendor to see if this may be a possible cause.
Jacco Schalkwijk wrote:
> Hi Ben,
> As the error is in a non-clustered index, you can just rebuild the index
> with DBCC DBREINDEX (<table_name>, <index_name>). Or if you want to be on
> the safe side, just drop and recreate the index.
> After that, investigate why the corruption occurred in the first place. You
> might have hardware problems.
>

Keys out of order on page error

Monday, March 12, 2012

key to db error code meaning?

on a server running SQL Server 2000 we occasionally get errors. the most
frequent ones in a recent profiler trace, for example, are 208 and 1205.
i've been searching in vain for an explanation of what these mean. i found a
key to severity, but not to the meaning of the errors themselves. can
someone please point me to a guide on the subject?
cheers,
Tim Hansontbh
Open an ERROR.LOG to see what is going on? Deadlocks?
"tbh" <femdev@.newsgroups.nospam> wrote in message
news:OypijxhbIHA.1208@.TK2MSFTNGP03.phx.gbl...
> on a server running SQL Server 2000 we occasionally get errors. the most
> frequent ones in a recent profiler trace, for example, are 208 and 1205.
> i've been searching in vain for an explanation of what these mean. i found
> a key to severity, but not to the meaning of the errors themselves. can
> someone please point me to a guide on the subject?
> cheers,
> Tim Hanson
>|||thanks. i was thinking more in terms of a table of definitions. i can find
hints, e.g., for error 208:
http://www.novicksoftware.com/TipsAndTricks/tip-sql-server-replication-208.htm
thought maybe there is a summary of error message meanings (or range
categories) along the lines of what I found for "severity".
the ones we get appear to be routine. there isn't much in the ERROR.LOGs.
thanks again,
tbh
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eAQscEibIHA.536@.TK2MSFTNGP06.phx.gbl...
> tbh
> Open an ERROR.LOG to see what is going on? Deadlocks?
>
>
> "tbh" <femdev@.newsgroups.nospam> wrote in message
> news:OypijxhbIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> on a server running SQL Server 2000 we occasionally get errors. the most
>> frequent ones in a recent profiler trace, for example, are 208 and 1205.
>> i've been searching in vain for an explanation of what these mean. i
>> found a key to severity, but not to the meaning of the errors themselves.
>> can someone please point me to a guide on the subject?
>> cheers,
>> Tim Hanson
>|||The generic error messages are in master.dbo.sysmessages. E.G.,
Select * From master.dbo.sysmessages Where error = 208
But when you actually get the errors, SQL Server will pass back a string as
well as the error message. This string will have the parameters replaced
with actual values. For example, error 208 is invalid object name, but when
you actually get the message, it will tell you which object name was
invalid. But if whatever connection method you are using is swallowing the
error text and only returning the error number, you can look it up in
sysmessages. You can also search on it in BOL. BOL doesn't have every
error, but it gives additional info about some of them.
Tom
"tbh" <femdev@.newsgroups.nospam> wrote in message
news:%23g3a4iibIHA.4144@.TK2MSFTNGP05.phx.gbl...
> thanks. i was thinking more in terms of a table of definitions. i can find
> hints, e.g., for error 208:
>
> http://www.novicksoftware.com/TipsAndTricks/tip-sql-server-replication-208.htm
> thought maybe there is a summary of error message meanings (or range
> categories) along the lines of what I found for "severity".
> the ones we get appear to be routine. there isn't much in the ERROR.LOGs.
> thanks again,
> tbh
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eAQscEibIHA.536@.TK2MSFTNGP06.phx.gbl...
>> tbh
>> Open an ERROR.LOG to see what is going on? Deadlocks?
>>
>>
>> "tbh" <femdev@.newsgroups.nospam> wrote in message
>> news:OypijxhbIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> on a server running SQL Server 2000 we occasionally get errors. the most
>> frequent ones in a recent profiler trace, for example, are 208 and 1205.
>> i've been searching in vain for an explanation of what these mean. i
>> found a key to severity, but not to the meaning of the errors
>> themselves. can someone please point me to a guide on the subject?
>> cheers,
>> Tim Hanson
>>
>

Friday, March 9, 2012

Keeping SQL 2000 databases in synch after restore.

We have 4 different databases, running on sql server 2000. I scheduled
the database to be backed up at 5:00, and every hour for the trans
log.
If we have a hardware failure and have to restore from backup. how do
I make sure that the database are all starting at the same time. (if
database common takes 2 min to back up and database customer takes 30
min to back up) how do I make sure these 2 database are at the same
point in time.
Is there anything I can do in my sql backup job to have the backup's
end at the same time?
Currently all of the backup are different jobs.
Nicholas.GadaczSo you're saying there are transactional integrity reasons why the DB's all
need to be restored to the exact same point in time?
RESTORE LOG allows you to specify STOPAT for point in time recovery, but
RESTORE DATABASE doesn't.
I can't think of a clean way to do that for a full database without using
tran log restores...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"First Tracks Research" <info@.ftresearch.com> wrote in message
news:4d02a84e.0401061017.1db0b24d@.posting.google.com...
> We have 4 different databases, running on sql server 2000. I scheduled
> the database to be backed up at 5:00, and every hour for the trans
> log.
> If we have a hardware failure and have to restore from backup. how do
> I make sure that the database are all starting at the same time. (if
> database common takes 2 min to back up and database customer takes 30
> min to back up) how do I make sure these 2 database are at the same
> point in time.
> Is there anything I can do in my sql backup job to have the backup's
> end at the same time?
> Currently all of the backup are different jobs.
> Nicholas.Gadacz|||I really don't mean to be a smart ass here. I realize it may not be the
answer you are looking for. But this is kind of a design issue. If there
are transactional integrity issues, the easiest and cleanest way is to put
those objects in the same database. You may have name conflicts, but those
will be much easier to resolve now than the issues you have started to run
into now. What if tomorrow you want to send a snapshot replication of five
related tables? You will have the same issue.
If you do take my advice and put your databases into one, a good way to
avoid naming conflicts in your code, is to script one of the databases in
its entirety, do a search and replace and change all occurences of
conflicting names at the object definition as well as the references in code
simultaneously. Then you can put the two together without conflict. Again,
if you decide to do that, I can be made available for assistance remotely.
Pls remove NO_SPAM(s) from my email if you need to reach me.
--
Ata R
Parvan Consulting Inc
NO_SPAMar_alias001@.NO_SPAMparvan.net
"First Tracks Research" <info@.ftresearch.com> wrote in message
news:4d02a84e.0401061017.1db0b24d@.posting.google.com...
> We have 4 different databases, running on sql server 2000. I scheduled
> the database to be backed up at 5:00, and every hour for the trans
> log.
> If we have a hardware failure and have to restore from backup. how do
> I make sure that the database are all starting at the same time. (if
> database common takes 2 min to back up and database customer takes 30
> min to back up) how do I make sure these 2 database are at the same
> point in time.
> Is there anything I can do in my sql backup job to have the backup's
> end at the same time?
> Currently all of the backup are different jobs.
> Nicholas.Gadacz

Monday, March 26, 2012

Killing a User Process

killing a process shows 0% completion time

killing a process shows 0% completion time

Killer problem with Scheduled Reports

Friday, March 23, 2012

kill process always shows 0% completion

Kill process

Kill process

Kill process

kill long running query

kill long running query

kill long running (hung) SQL task

Wednesday, March 21, 2012

Kill command don't work

Kill command don't work

Kill command don't work

Kicking off a job on another SQL server

Monday, March 19, 2012

Keys out of order on page error

Keys out of order on page error

Keys out of order on page error

Monday, March 12, 2012

key to db error code meaning?

Friday, March 9, 2012

Keeping SQL 2000 databases in synch after restore.

KDC Problem

Blog Archive

About Me