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