When I try to stop a process which running command 'UPDATE STATISTICS
table_name WITH SAMPLE 50 PERCENT' with 'kill spid', then run sp_who2, the
output window shows that this process is 'KILLED/ROLLBACK'. It looks like it
is continue updating statistics.
Why 'Cancel Query' command in the query analyser works, but kill command
does not wok?
How can I stop this process?
Thanks,
LijunHi,
Kill command is always safe and will rollback all the transaction.
Thanks
Hari
MCDBA
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:e3jW7Iq6DHA.3648@.TK2MSFTNGP11.phx.gbl...
> When I try to stop a process which running command 'UPDATE STATISTICS
> table_name WITH SAMPLE 50 PERCENT' with 'kill spid', then run sp_who2, the
> output window shows that this process is 'KILLED/ROLLBACK'. It looks like
it
> is continue updating statistics.
> Why 'Cancel Query' command in the query analyser works, but kill command
> does not wok?
> How can I stop this process?
> Thanks,
> Lijun
>|||Hi Lijun,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you information, when you run 'UPDATE STATISTICS table_name WITH
SAMPLE 50 PERCENT' and kill this process. But when run sp_who2, you found
that the process is 'KILLED/ROLLBACK'. But you think you might not seen
this process when running sp_who2, right?
Is this problem always happen? Could you provide the information which
edition of SQL Server you are using and could you please provide me the
table definition by right-click the table and choose 'all task' and choose
'genterate SQL script'? Or just by running
use database_name
go
exec sp_help table_name
Waiting for you reply and thank you for you post.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I can reproduce this problem regardless which table I use. Our server
edition is:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:9yJvtc56DHA.2164@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> From you information, when you run 'UPDATE STATISTICS table_name WITH
> SAMPLE 50 PERCENT' and kill this process. But when run sp_who2, you found
> that the process is 'KILLED/ROLLBACK'. But you think you might not seen
> this process when running sp_who2, right?
> Is this problem always happen? Could you provide the information which
> edition of SQL Server you are using and could you please provide me the
> table definition by right-click the table and choose 'all task' and choose
> 'genterate SQL script'? Or just by running
> use database_name
> go
> exec sp_help table_name
> Waiting for you reply and thank you for you post.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lijun,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As far as I know, when you are runing T-SQL command as 'UPDATE STATISTICS
table_name WITH SAMPLE 50 PERCENT', by default, one singel T-SQL statement
will be a transaction without explicitly claim. If it will take many
actions within this transaction within it, when killed this process by
'KILL SPID' it will take some time to rollback all the actions it has
taken. So, from the sp_who2, you will notice that the status is
'KILLED/ROLLBACK'; If it is still running in the Query Analyzer and you
press 'Cancel Query Execution' button, it will also be stoped and be rolled
back. For the rolled back process, it will take time depends on what it has
done before it is killed/stopped when it begin. So, in Query Analyzer and
for 'Kill', both will work and stop the 'update statistics' and rolled it
back.
Hope this answered you questions and if you still have questions, please
feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I don't think it is really ROLLBACK. For example, if UPDATE STATISTICS WITH
FULLSCAN on a table takes one hour, pressing 'Cancel Query Excution' button
will stop the process immediately. But if you using KILL command, even you
just started the UPDATE STATISTICS two second ago, it will continue run
until the procees is done. If you issues two UPDATE STATISTICS on a single
batch, it will stop until both table is done. You can check the result with
STATS_DATE, and it shows that both table's statistics have been updated.
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:jueBFVK7DHA.808@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> As far as I know, when you are runing T-SQL command as 'UPDATE STATISTICS
> table_name WITH SAMPLE 50 PERCENT', by default, one singel T-SQL
statement
> will be a transaction without explicitly claim. If it will take many
> actions within this transaction within it, when killed this process by
> 'KILL SPID' it will take some time to rollback all the actions it has
> taken. So, from the sp_who2, you will notice that the status is
> 'KILLED/ROLLBACK'; If it is still running in the Query Analyzer and you
> press 'Cancel Query Execution' button, it will also be stoped and be
rolled
> back. For the rolled back process, it will take time depends on what it
has
> done before it is killed/stopped when it begin. So, in Query Analyzer and
> for 'Kill', both will work and stop the 'update statistics' and rolled it
> back.
> Hope this answered you questions and if you still have questions, please
> feel free to post new message here and I am ready to help!
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||U can also use
kill spid with statusonly to tell U how much percentage rollback is left . . .|||Hi Lijun,
Thank you for your update and it is my pleasure to help you with you issue.
I need to make some explanation here. When a transaction is terminated
before it completed, it must be roll back. Also, when a transaction is
cancelled, the rollback time will not based on how long this transation it
has run. In Query Analyzer, when you run 'UPDATE STATISTICS...' and then
press' Cancel Query Execution', the transaction is terminated within the
same thread. Could you run the Stats_date of DBCC SHOWSTATISTICS to check
that, if the 'UPDATE STATISTICS' is running and you press the 'Cancel Query
Execution' button, is the 'UPDATE STATISTICS' rolled back'
When you kill a SPID, a kill command is sent to the SPID, that is, one
thread will send a KILL command to another thread, it is not guaranteed
that the SPID is killed. A spid may respond to the KILL command
immediately, or after a delay, or not at all.
Please refer to the following KB:
INF: Understanding How the Transact-SQL KILL Command Works
http://support.microsoft.com/?id=171224
When you kill a SPID running 'UPDATE STATISTICS, could you check the status
by DBCC PSS? What the status of it? When you restart the SQL Server Service
and restart it, when using Stats_date of DBCC SHOWSTATISTICS, how about the
'Updated' time?
I am looking forward to your reply. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for you reply. I suggest that you try to reproduce the scenario by
yourself (It is not difficult to do). By the way, the article you motioned
is apply to SQL Server 6.0 and 6.5.
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:IkW$Pnt7DHA.568@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for your update and it is my pleasure to help you with you
issue.
> I need to make some explanation here. When a transaction is terminated
> before it completed, it must be roll back. Also, when a transaction is
> cancelled, the rollback time will not based on how long this transation it
> has run. In Query Analyzer, when you run 'UPDATE STATISTICS...' and then
> press' Cancel Query Execution', the transaction is terminated within the
> same thread. Could you run the Stats_date of DBCC SHOWSTATISTICS to check
> that, if the 'UPDATE STATISTICS' is running and you press the 'Cancel
Query
> Execution' button, is the 'UPDATE STATISTICS' rolled back'
> When you kill a SPID, a kill command is sent to the SPID, that is, one
> thread will send a KILL command to another thread, it is not guaranteed
> that the SPID is killed. A spid may respond to the KILL command
> immediately, or after a delay, or not at all.
> Please refer to the following KB:
> INF: Understanding How the Transact-SQL KILL Command Works
> http://support.microsoft.com/?id=171224
> When you kill a SPID running 'UPDATE STATISTICS, could you check the
status
> by DBCC PSS? What the status of it? When you restart the SQL Server
Service
> and restart it, when using Stats_date of DBCC SHOWSTATISTICS, how about
the
> 'Updated' time?
> I am looking forward to your reply. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lijun,
Thank you very much for your update!
Yes, I create a huge database and tested what you have done. Yes, you are
right that when using the KILL to kill the SPID running 'UPDATE
STATISTICS', although SP_WHO2 showed the command is KILLED/ROLLBACK and I
could even got the information indicating that it is rolled back 100% and
estimated time for roll back is 1 second. However, the 'UPDATE STATISTICS'
is not stopped by the KILL command.
I tried to figure it out and it really took me some time. Later, I
discussed it with my senior engineers in my team and I got the some
information of it. It is a known issue of SQL Server. I should appologize
that in my last reply to your question, I did not provide you the right
answer, and I did not get the information of it in time. Could I provide
some more assistance this time. I mean if you have any bussiness impact
from it, please feel free to post here and I would try my best to help you!
Thank you very much.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Can you please share with us what the reason for this problem is and how it can be solved?|||Hi Lijun,
Thank you for your update. Our development team is aware of it and is
working on that, I cannot guarantee anything of it. I will update you as
soon as possilbe for the progress of it. Thanks for you understanding.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
