Can u pls help with the trasaction isolation levels

SET TRANSACTION ISOLATION LEVEL issue:
which one i can use in a vb code which uses openrow set to
acesss data in sqlsever and as such it is blocking other
process so which trnsaction isolation level i can specify
so that it doesnt block others and its a read only procees
thanksHi Saradhi,
You can try READ UNCOMMITED. This isolation level allows readers of data to
read uncommited (durty) records from the database.
HTH
Karl Gram
http://www.gramonline.com
"saradhi" <anonymous@.discussions.microsoft.com> wrote in message
news:dc7c01c40b3c$a1e8ac20$a501280a@.phx.gbl...
> SET TRANSACTION ISOLATION LEVEL issue:
> which one i can use in a vb code which uses openrow set to
> acesss data in sqlsever and as such it is blocking other
> process so which trnsaction isolation level i can specify
> so that it doesnt block others and its a read only procees
> thanks|||sradhi
If you can provide a little bit more info about what are you trying to
accomlish so it helps us to solve the problem
Let me say you have a SELECT statement and later you decided to update a
selected rows. You have to make sure that others users could not change the
data while you select it. Specify UPDLOCK hint which dont block others users
from reading the selected rows but you can be assured that data has not
changed since last you read it.
"saradhi" <anonymous@.discussions.microsoft.com> wrote in message
news:dc7c01c40b3c$a1e8ac20$a501280a@.phx.gbl...
> SET TRANSACTION ISOLATION LEVEL issue:
> which one i can use in a vb code which uses openrow set to
> acesss data in sqlsever and as such it is blocking other
> process so which trnsaction isolation level i can specify
> so that it doesnt block others and its a read only procees
> thanks|||thanks i will go for read uncommited
OK the problem is we have a Dell PowerEdge 2650 Server
with win2k and sql server 2003 installed and the server
has all the default configuraion settings in sql server
i am facing problem with openrow set queries used by some
of the applications which is blocking other process i
check for why it is happenning i got the bug was with
the fiber mode option which i had enabled in sql server
settings later i had removed it as my ram is 2 gb and its
bogging u p my server now its on thread mode but still
i have problems in openrowset quiroes some times blocking
other process they go into ana infinite loop even we kill
the procees the rollback goes on and on it s bug with
fiber mode option as said in one of the tech docs in tech
net ,later i have gone for reinstalling th mdac but still
now has any one faced this problem and can anyone suggest
me how to resolve it pls
thanks
saradhi

>--Original Message--
>sradhi
>If you can provide a little bit more info about what are
you trying to
>accomlish so it helps us to solve the problem
>Let me say you have a SELECT statement and later you
decided to update a
>selected rows. You have to make sure that others users
could not change the
>data while you select it. Specify UPDLOCK hint which dont
block others users
>from reading the selected rows but you can be assured
that data has not
>changed since last you read it.
>
>"saradhi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:dc7c01c40b3c$a1e8ac20$a501280a@.phx.gbl...
to
specify
procees
>
>.
>|||Saradi
<http://support.microsoft.com/direct...B;EN-US;Q224453>--
-- INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems (Q224453)
Also I would not recommend you to change the default setting. Are you aware
that your users can get dirty data which may not written to disk at all?
I'd go with reviewing your application , how does it work, how does it
access to the objects. Pehaps you have long-run queries which cause the
bloking.

"saradhi" <anonymous@.discussions.microsoft.com> wrote in message
news:b05e01c40b42$d63f8290$a601280a@.phx.gbl...
> thanks i will go for read uncommited
> OK the problem is we have a Dell PowerEdge 2650 Server
> with win2k and sql server 2003 installed and the server
> has all the default configuraion settings in sql server
> i am facing problem with openrow set queries used by some
> of the applications which is blocking other process i
> check for why it is happenning i got the bug was with
> the fiber mode option which i had enabled in sql server
> settings later i had removed it as my ram is 2 gb and its
> bogging u p my server now its on thread mode but still
> i have problems in openrowset quiroes some times blocking
> other process they go into ana infinite loop even we kill
> the procees the rollback goes on and on it s bug with
> fiber mode option as said in one of the tech docs in tech
> net ,later i have gone for reinstalling th mdac but still
> now has any one faced this problem and can anyone suggest
> me how to resolve it pls
> thanks
> saradhi
>
> you trying to
> decided to update a
> could not change the
> block others users
> that data has not
> message
> to
> specify
> procees

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

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

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: