I just had tons of blocking in my DB. I tracked down the culprit and killed
the spid... mistakingly without running dbcc inputbuffer first. So I the
looked in the error log and am 99% sure that what I killed was a TLog backup
based on the message:
BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
I/O operation has been aborted because of either a thread exit or an
application request.).
So my question is can a TLog backup cause blocking. I see how a TLog backup
could be blocked if a big transaction was occuring when it was run... but
could that in turn block other user activity? If so, how do I prevent this
from happening again?
TIA, ChrisRHi,
As far i am concerned Transaction log backup will not block any users. It
can only block if you execute 2 transaction log backups in parallel for the
same database or if both full database backup and transaction log backup
runs in parallel on the same database.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>|||ChrisR wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and
> killed the spid... mistakingly without running dbcc inputbuffer
> first. So I the looked in the error log and am 99% sure that what I
> killed was a TLog backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a
> thread exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how
> do I prevent this from happening again?
>
> TIA, ChrisR
The t-log backup only affects committed transactions. Open transactions
are not backed up. If you are backing up the log to the same drive where
you keep data, indexes, or other databases, it's possible you are
causing undue disk activity which could cause all transactions to slow
down. How long does the t-log backup normally take? Where are you
backing up?
--
David Gugick
Imceda Software
www.imceda.com|||The backup normally takes 30 - 60 seconds. I am backing up locally... but
not to the data drive.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%230ImFwhYFHA.4024@.TK2MSFTNGP10.phx.gbl...
> ChrisR wrote:
>> I just had tons of blocking in my DB. I tracked down the culprit and
>> killed the spid... mistakingly without running dbcc inputbuffer
>> first. So I the looked in the error log and am 99% sure that what I
>> killed was a TLog backup based on the message:
>> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
>> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
>> error 995(The I/O operation has been aborted because of either a
>> thread exit or an application request.).
>>
>> So my question is can a TLog backup cause blocking. I see how a TLog
>> backup could be blocked if a big transaction was occuring when it was
>> run... but could that in turn block other user activity? If so, how
>> do I prevent this from happening again?
>>
>> TIA, ChrisR
> The t-log backup only affects committed transactions. Open transactions
> are not backed up. If you are backing up the log to the same drive where
> you keep data, indexes, or other databases, it's possible you are causing
> undue disk activity which could cause all transactions to slow down. How
> long does the t-log backup normally take? Where are you backing up?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Another question, I did see "set textsize" occuring during this block. Ive
never used and and was wondering if it could be the culprit.
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>|||ChrisR wrote:
> Another question, I did see "set textsize" occuring during this
> block. Ive never used and and was wondering if it could be the
> culprit.
That statement just determines how much data from text columns is
returned with a SELECT statement. If anything, it should quicken queries
that access text/ntext data if the value is set lower than the average
number of bytes in the column.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Try to use 1204 trace flag to get more info about locks next time.
Look if you would have there BULK-OP-LOG locks - this particular type
of locks caused by transaction log backup. But as already been mentioned
it shouldn't be an issue to the uncommited transactions.
And usually this type of locks shouldn't cause any issues at all.
Regards.
"ChrisR" wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and killed
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog backup
> based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backup
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>|||Transaction log backups can cause long transactions. I see stable pattern,
when during backup of transaction log on different disk from log disk or data
files disks, stored procedures which work, average, 30 ms, run near 2.5-3 sec.
"ChrisR" wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and killed
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog backup
> based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backup
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>|||The reason most people see performance issues with Log backups is because
their disk subsystem is not up to par, especially the one the log file is
on. Since the Log file must be read to back it up if that causes a lot of
disk queuing your system will suffer overall. If the log backup is causing a
bottleneck on that drive all new log writes will be affected. Since all log
activity is sequential it starts to affect all transactions from that point
on until the backup is done and the bottleneck is gone. This is not a Log
or Backup issue but an inadequately configured I/O system.
--
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:8CEB19AF-0F9B-4C52-9DDF-711A533C6BFA@.microsoft.com...
> Transaction log backups can cause long transactions. I see stable pattern,
> when during backup of transaction log on different disk from log disk or
> data
> files disks, stored procedures which work, average, 30 ms, run near 2.5-3
> sec.
> "ChrisR" wrote:
>> I just had tons of blocking in my DB. I tracked down the culprit and
>> killed
>> the spid... mistakingly without running dbcc inputbuffer first. So I the
>> looked in the error log and am 99% sure that what I killed was a TLog
>> backup
>> based on the message:
>> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
>> device
>> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error
>> 995(The
>> I/O operation has been aborted because of either a thread exit or an
>> application request.).
>>
>> So my question is can a TLog backup cause blocking. I see how a TLog
>> backup
>> could be blocked if a big transaction was occuring when it was run... but
>> could that in turn block other user activity? If so, how do I prevent
>> this
>> from happening again?
>>
>> TIA, ChrisR
>>
>>sql
Showing posts with label spid. Show all posts
Showing posts with label spid. Show all posts
Tuesday, March 20, 2012
can TLog backups cause blocking
I just had tons of blocking in my DB. I tracked down the culprit and killed
the spid... mistakingly without running dbcc inputbuffer first. So I the
looked in the error log and am 99% sure that what I killed was a TLog backup
based on the message:
BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup device
'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
I/O operation has been aborted because of either a thread exit or an
application request.).
So my question is can a TLog backup cause blocking. I see how a TLog backup
could be blocked if a big transaction was occuring when it was run... but
could that in turn block other user activity? If so, how do I prevent this
from happening again?
TIA, ChrisRHi,
As far i am concerned Transaction log backup will not block any users. It
can only block if you execute 2 transaction log backups in parallel for the
same database or if both full database backup and transaction log backup
runs in parallel on the same database.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>|||ChrisR wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and
> killed the spid... mistakingly without running dbcc inputbuffer
> first. So I the looked in the error log and am 99% sure that what I
> killed was a TLog backup based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a
> thread exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how
> do I prevent this from happening again?
>
> TIA, ChrisR
The t-log backup only affects committed transactions. Open transactions
are not backed up. If you are backing up the log to the same drive where
you keep data, indexes, or other databases, it's possible you are
causing undue disk activity which could cause all transactions to slow
down. How long does the t-log backup normally take? Where are you
backing up?
David Gugick
Imceda Software
www.imceda.com|||The backup normally takes 30 - 60 seconds. I am backing up locally... but
not to the data drive.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%230ImFwhYFHA.4024@.TK2MSFTNGP10.phx.gbl...
> ChrisR wrote:
> The t-log backup only affects committed transactions. Open transactions
> are not backed up. If you are backing up the log to the same drive where
> you keep data, indexes, or other databases, it's possible you are causing
> undue disk activity which could cause all transactions to slow down. How
> long does the t-log backup normally take? Where are you backing up?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Another question, I did see "set textsize" occuring during this block. Ive
never used and and was wondering if it could be the culprit.
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>|||ChrisR wrote:
> Another question, I did see "set textsize" occuring during this
> block. Ive never used and and was wondering if it could be the
> culprit.
That statement just determines how much data from text columns is
returned with a SELECT statement. If anything, it should quicken queries
that access text/ntext data if the value is set lower than the average
number of bytes in the column.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Try to use 1204 trace flag to get more info about locks next time.
Look if you would have there BULK-OP-LOG locks - this particular type
of locks caused by transaction log backup. But as already been mentioned
it shouldn't be an issue to the uncommited transactions.
And usually this type of locks shouldn't cause any issues at all.
Regards.
"ChrisR" wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and kille
d
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog back
up
> based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup devi
ce
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(T
he
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backu
p
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>|||Transaction log backups can cause long transactions. I see stable pattern,
when during backup of transaction log on different disk from log disk or dat
a
files disks, stored procedures which work, average, 30 ms, run near 2.5-3 se
c.
"ChrisR" wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and kille
d
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog back
up
> based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup devi
ce
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(T
he
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backu
p
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>|||The reason most people see performance issues with Log backups is because
their disk subsystem is not up to par, especially the one the log file is
on. Since the Log file must be read to back it up if that causes a lot of
disk queuing your system will suffer overall. If the log backup is causing a
bottleneck on that drive all new log writes will be affected. Since all log
activity is sequential it starts to affect all transactions from that point
on until the backup is done and the bottleneck is gone. This is not a Log
or Backup issue but an inadequately configured I/O system.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:8CEB19AF-0F9B-4C52-9DDF-711A533C6BFA@.microsoft.com...[vbcol=seagreen]
> Transaction log backups can cause long transactions. I see stable pattern,
> when during backup of transaction log on different disk from log disk or
> data
> files disks, stored procedures which work, average, 30 ms, run near 2.5-3
> sec.
> "ChrisR" wrote:
>
the spid... mistakingly without running dbcc inputbuffer first. So I the
looked in the error log and am 99% sure that what I killed was a TLog backup
based on the message:
BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup device
'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
I/O operation has been aborted because of either a thread exit or an
application request.).
So my question is can a TLog backup cause blocking. I see how a TLog backup
could be blocked if a big transaction was occuring when it was run... but
could that in turn block other user activity? If so, how do I prevent this
from happening again?
TIA, ChrisRHi,
As far i am concerned Transaction log backup will not block any users. It
can only block if you execute 2 transaction log backups in parallel for the
same database or if both full database backup and transaction log backup
runs in parallel on the same database.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>|||ChrisR wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and
> killed the spid... mistakingly without running dbcc inputbuffer
> first. So I the looked in the error log and am 99% sure that what I
> killed was a TLog backup based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a
> thread exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how
> do I prevent this from happening again?
>
> TIA, ChrisR
The t-log backup only affects committed transactions. Open transactions
are not backed up. If you are backing up the log to the same drive where
you keep data, indexes, or other databases, it's possible you are
causing undue disk activity which could cause all transactions to slow
down. How long does the t-log backup normally take? Where are you
backing up?
David Gugick
Imceda Software
www.imceda.com|||The backup normally takes 30 - 60 seconds. I am backing up locally... but
not to the data drive.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%230ImFwhYFHA.4024@.TK2MSFTNGP10.phx.gbl...
> ChrisR wrote:
> The t-log backup only affects committed transactions. Open transactions
> are not backed up. If you are backing up the log to the same drive where
> you keep data, indexes, or other databases, it's possible you are causing
> undue disk activity which could cause all transactions to slow down. How
> long does the t-log backup normally take? Where are you backing up?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Another question, I did see "set textsize" occuring during this block. Ive
never used and and was wondering if it could be the culprit.
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>|||ChrisR wrote:
> Another question, I did see "set textsize" occuring during this
> block. Ive never used and and was wondering if it could be the
> culprit.
That statement just determines how much data from text columns is
returned with a SELECT statement. If anything, it should quicken queries
that access text/ntext data if the value is set lower than the average
number of bytes in the column.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Try to use 1204 trace flag to get more info about locks next time.
Look if you would have there BULK-OP-LOG locks - this particular type
of locks caused by transaction log backup. But as already been mentioned
it shouldn't be an issue to the uncommited transactions.
And usually this type of locks shouldn't cause any issues at all.
Regards.
"ChrisR" wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and kille
d
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog back
up
> based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup devi
ce
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(T
he
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backu
p
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>|||Transaction log backups can cause long transactions. I see stable pattern,
when during backup of transaction log on different disk from log disk or dat
a
files disks, stored procedures which work, average, 30 ms, run near 2.5-3 se
c.
"ChrisR" wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and kille
d
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog back
up
> based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup devi
ce
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(T
he
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backu
p
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>|||The reason most people see performance issues with Log backups is because
their disk subsystem is not up to par, especially the one the log file is
on. Since the Log file must be read to back it up if that causes a lot of
disk queuing your system will suffer overall. If the log backup is causing a
bottleneck on that drive all new log writes will be affected. Since all log
activity is sequential it starts to affect all transactions from that point
on until the backup is done and the bottleneck is gone. This is not a Log
or Backup issue but an inadequately configured I/O system.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:8CEB19AF-0F9B-4C52-9DDF-711A533C6BFA@.microsoft.com...[vbcol=seagreen]
> Transaction log backups can cause long transactions. I see stable pattern,
> when during backup of transaction log on different disk from log disk or
> data
> files disks, stored procedures which work, average, 30 ms, run near 2.5-3
> sec.
> "ChrisR" wrote:
>
can TLog backups cause blocking
I just had tons of blocking in my DB. I tracked down the culprit and killed
the spid... mistakingly without running dbcc inputbuffer first. So I the
looked in the error log and am 99% sure that what I killed was a TLog backup
based on the message:
BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
I/O operation has been aborted because of either a thread exit or an
application request.).
So my question is can a TLog backup cause blocking. I see how a TLog backup
could be blocked if a big transaction was occuring when it was run... but
could that in turn block other user activity? If so, how do I prevent this
from happening again?
TIA, ChrisR
Hi,
As far i am concerned Transaction log backup will not block any users. It
can only block if you execute 2 transaction log backups in parallel for the
same database or if both full database backup and transaction log backup
runs in parallel on the same database.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>
|||ChrisR wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and
> killed the spid... mistakingly without running dbcc inputbuffer
> first. So I the looked in the error log and am 99% sure that what I
> killed was a TLog backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a
> thread exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how
> do I prevent this from happening again?
>
> TIA, ChrisR
The t-log backup only affects committed transactions. Open transactions
are not backed up. If you are backing up the log to the same drive where
you keep data, indexes, or other databases, it's possible you are
causing undue disk activity which could cause all transactions to slow
down. How long does the t-log backup normally take? Where are you
backing up?
David Gugick
Imceda Software
www.imceda.com
|||The backup normally takes 30 - 60 seconds. I am backing up locally... but
not to the data drive.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%230ImFwhYFHA.4024@.TK2MSFTNGP10.phx.gbl...
> ChrisR wrote:
> The t-log backup only affects committed transactions. Open transactions
> are not backed up. If you are backing up the log to the same drive where
> you keep data, indexes, or other databases, it's possible you are causing
> undue disk activity which could cause all transactions to slow down. How
> long does the t-log backup normally take? Where are you backing up?
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Another question, I did see "set textsize" occuring during this block. Ive
never used and and was wondering if it could be the culprit.
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>
|||ChrisR wrote:
> Another question, I did see "set textsize" occuring during this
> block. Ive never used and and was wondering if it could be the
> culprit.
That statement just determines how much data from text columns is
returned with a SELECT statement. If anything, it should quicken queries
that access text/ntext data if the value is set lower than the average
number of bytes in the column.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Try to use 1204 trace flag to get more info about locks next time.
Look if you would have there BULK-OP-LOG locks - this particular type
of locks caused by transaction log backup. But as already been mentioned
it shouldn't be an issue to the uncommited transactions.
And usually this type of locks shouldn't cause any issues at all.
Regards.
"ChrisR" wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and killed
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog backup
> based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backup
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>
|||Transaction log backups can cause long transactions. I see stable pattern,
when during backup of transaction log on different disk from log disk or data
files disks, stored procedures which work, average, 30 ms, run near 2.5-3 sec.
"ChrisR" wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and killed
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog backup
> based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backup
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>
|||The reason most people see performance issues with Log backups is because
their disk subsystem is not up to par, especially the one the log file is
on. Since the Log file must be read to back it up if that causes a lot of
disk queuing your system will suffer overall. If the log backup is causing a
bottleneck on that drive all new log writes will be affected. Since all log
activity is sequential it starts to affect all transactions from that point
on until the backup is done and the bottleneck is gone. This is not a Log
or Backup issue but an inadequately configured I/O system.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:8CEB19AF-0F9B-4C52-9DDF-711A533C6BFA@.microsoft.com...[vbcol=seagreen]
> Transaction log backups can cause long transactions. I see stable pattern,
> when during backup of transaction log on different disk from log disk or
> data
> files disks, stored procedures which work, average, 30 ms, run near 2.5-3
> sec.
> "ChrisR" wrote:
the spid... mistakingly without running dbcc inputbuffer first. So I the
looked in the error log and am 99% sure that what I killed was a TLog backup
based on the message:
BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
I/O operation has been aborted because of either a thread exit or an
application request.).
So my question is can a TLog backup cause blocking. I see how a TLog backup
could be blocked if a big transaction was occuring when it was run... but
could that in turn block other user activity? If so, how do I prevent this
from happening again?
TIA, ChrisR
Hi,
As far i am concerned Transaction log backup will not block any users. It
can only block if you execute 2 transaction log backups in parallel for the
same database or if both full database backup and transaction log backup
runs in parallel on the same database.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>
|||ChrisR wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and
> killed the spid... mistakingly without running dbcc inputbuffer
> first. So I the looked in the error log and am 99% sure that what I
> killed was a TLog backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a
> thread exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how
> do I prevent this from happening again?
>
> TIA, ChrisR
The t-log backup only affects committed transactions. Open transactions
are not backed up. If you are backing up the log to the same drive where
you keep data, indexes, or other databases, it's possible you are
causing undue disk activity which could cause all transactions to slow
down. How long does the t-log backup normally take? Where are you
backing up?
David Gugick
Imceda Software
www.imceda.com
|||The backup normally takes 30 - 60 seconds. I am backing up locally... but
not to the data drive.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%230ImFwhYFHA.4024@.TK2MSFTNGP10.phx.gbl...
> ChrisR wrote:
> The t-log backup only affects committed transactions. Open transactions
> are not backed up. If you are backing up the log to the same drive where
> you keep data, indexes, or other databases, it's possible you are causing
> undue disk activity which could cause all transactions to slow down. How
> long does the t-log backup normally take? Where are you backing up?
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Another question, I did see "set textsize" occuring during this block. Ive
never used and and was wondering if it could be the culprit.
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>
|||ChrisR wrote:
> Another question, I did see "set textsize" occuring during this
> block. Ive never used and and was wondering if it could be the
> culprit.
That statement just determines how much data from text columns is
returned with a SELECT statement. If anything, it should quicken queries
that access text/ntext data if the value is set lower than the average
number of bytes in the column.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Try to use 1204 trace flag to get more info about locks next time.
Look if you would have there BULK-OP-LOG locks - this particular type
of locks caused by transaction log backup. But as already been mentioned
it shouldn't be an issue to the uncommited transactions.
And usually this type of locks shouldn't cause any issues at all.
Regards.
"ChrisR" wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and killed
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog backup
> based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backup
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>
|||Transaction log backups can cause long transactions. I see stable pattern,
when during backup of transaction log on different disk from log disk or data
files disks, stored procedures which work, average, 30 ms, run near 2.5-3 sec.
"ChrisR" wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and killed
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog backup
> based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backup
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>
|||The reason most people see performance issues with Log backups is because
their disk subsystem is not up to par, especially the one the log file is
on. Since the Log file must be read to back it up if that causes a lot of
disk queuing your system will suffer overall. If the log backup is causing a
bottleneck on that drive all new log writes will be affected. Since all log
activity is sequential it starts to affect all transactions from that point
on until the backup is done and the bottleneck is gone. This is not a Log
or Backup issue but an inadequately configured I/O system.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:8CEB19AF-0F9B-4C52-9DDF-711A533C6BFA@.microsoft.com...[vbcol=seagreen]
> Transaction log backups can cause long transactions. I see stable pattern,
> when during backup of transaction log on different disk from log disk or
> data
> files disks, stored procedures which work, average, 30 ms, run near 2.5-3
> sec.
> "ChrisR" wrote:
Friday, February 10, 2012
Can not kill a process
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.
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.
Can not kill a process
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...
it
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...
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.
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...
quote:
> 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
quote:|||Hi Lijun,
> 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
>
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...
quote:|||Hi 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.
>
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.
Subscribe to:
Posts (Atom)