SQL Server Performance Forum – Threads Archive
BULKOP_BACKUP_DB(LOG) locks
Hi folks, I am trying to get some info about these types of locks and how they behave from performance point of view in SQL2005. At the moment what I know is that BULKOP_BACKUP_DB can appear when you: 1) Do what described in BOL, i.e. a backup2) Have insufficient disk space in the db file and have autogrow option set to ON and your server is trying to get more space.
3) Do a bulk insert But it is certainly something else: at the moment I see a lot of them in tempdb on one of my production servers and it only appear under a heavy load. And there is no active backups, no autogrow settings and no bulk inserts into tempdb…so what can cause those types of locks ?
I don’t think your interpretation for this is correct…
As per BOL "Used for synchronization of database log backups with bulk operations."
I beleive it is used to for backup operations or lazy write operations to manage bulk operations in sync with backups/lazy writers operations…
Mohammed U.
I believe that bulk operations are only one of many cases when it can happen. Beside a situation when your DB is trying to get some disk space which can be related to sync between bulk op(getting space)
and lazy writing in my case users that put this lock have rights only to run couple of sp, and there is nothing about any bulk op in these procedures. And autogrow is off. From my experience with SQL2000 this type of lock effectively lock entire DB for everybody – in SQL2005 it seems a bit different but at the moment I see a clear link between DB slowness and BULKOP_BACKUP_DB locks appearance… I have a feeling that it can be caused by extensive usage of tempdb and will try to split it between more HDD spindles – although it is in SIMPLE recovery mode and I don’t see any high disk queue…
Yes the BULK insert operations do use the TEMPDB extensively, check the DMV sys.dm_tran_locks values at the same time. Also try to scheduel the backup during less traffice where any bulk load operations are not occuring at the same time, for a test. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Hi satya, Sorry to explain it a bit incorrectly – the problem is that in time when BULKOP_BACKUP_DB appears nobody is doing bulk operations or backups.
But what interested me much more than how they appear is what impact they (BULK locks) have on other users, i.e. is it a global lock that affect database performance for everybody or is it a local lock on some user-specific objects?
Hi again, just found that I have snapshot isolation level turned on – any thoughts can it be a reason?
I don’t use this isolation level explicitly in the code, but…
BOL explains
quote:Snapshot isolation provides transaction-level read consistency. A data snapshot is taken when the snapshot transaction starts, and remains consistent for the duration of the transaction. Use snapshot isolation when:
Optimistic concurrency control is desired.
Probability is low that a transaction would have to be rolled back because of an update conflict.
An application needs to generate reports based on long-running, multi-statement queries that must have point-in-time consistency. Snapshot isolation provides the benefit of repeatable reads (see Concurrency Effects) without using shared locks. Database snapshot can provide similar functionality but must be implemented manually. Snapshot isolation automatically provides the latest information in the database for each snapshot isolation transaction.
A new implementation of read committed isolation that uses row versioning when the READ_COMMITTED_SNAPSHOT database option is ON.
Probability is low that a transaction would have to be rolled back because of an update conflict.
An application needs to generate reports based on long-running, multi-statement queries that must have point-in-time consistency. Snapshot isolation provides the benefit of repeatable reads (see Concurrency Effects) without using shared locks. Database snapshot can provide similar functionality but must be implemented manually. Snapshot isolation automatically provides the latest information in the database for each snapshot isolation transaction.
A new isolation level, snapshot, that is enabled when the ALLOW_SNAPSHOT_ISOLATION database option is ON.
So check whether these options are enabled within any script. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>