Friday, February 17, 2012

DB Lock

Comming across the [BULK-OP-DB],[BULK-OP-LOG] , locks in the database and there is no database backup job running. Auto stats update is set to off. Auto shrink is also set to off.
Can someone shed more light on this ? and its implications on performance ?
DB:db_id[BULK-OP-DB], which identifies the database lock taken by the backup database.
DB:db_id[BULK-OP-LOG], which identifies the lock taken by the backup log for that particular database.
Thanks !!
It would depend on the kind of lock.
If the BULK-OP-DB lock is
1. LCK_M_X (exclusive):- cause may be database or file backup, incremental
db backup, or dbcc checkalloc/dbcc checkdb
2. LCK_M_S (shared):- cause may be index creation, text modification
operations, or nonlogged bulk insert/bcp/select into
3. LCK_M_U (update):- cause may be filegroup creation, shrink file/shrink
database, drop file, or add file
4. LCK_M_NL (NULL):- cause may be modify file, auto grow file, or tran log
backup/truncation
Hope that helps!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

No comments:

Post a Comment