Sql-server – SQL Server blocking report with waitresource Database

blockinglockingsql serversql-server-2012

We recently had a number of unexpected blocks occur on one of our servers. We captured the blocked process report and they are showing the waitresource as waitresource="DATABASE: 12:0". Below is an example of one of the reports. 

<blocked-process-report monitorLoop="45476">
 <blocked-process>
  <process id="process7db4d70c8" taskpriority="0" logused="10000" waitresource="DATABASE: 12:0 " waittime="24750" schedulerid="4" kpid="4556" status="suspended" spid="96" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="1900-01-01T00:00:00" lastbatchcompleted="1900-01-01T00:00:00" lastattention="1900-01-01T00:00:00" clientapp="App1" hostname="ServerMainFrame.domain.COM" hostpid="0" loginname="LoginA" isolationlevel="read committed (2)" xactid="0" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="suspended" waitresource="DATABASE: 12:0 " waittime="24998" spid="150" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="1900-01-01T00:00:00" lastbatchcompleted="1900-01-01T00:00:00" lastattention="1900-01-01T00:00:00" clientapp="Microsoft JDBC Driver for SQL Server" hostname="Server02.domain.com" hostpid="0" loginname="loginA" isolationlevel="read committed (2)" xactid="0" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

I was not even sure this could be a waitresource or sure what it means. Generally, the waitresource I see is a table, page, row, etc. The obvious would be that there is a lock on the database and nothing can use it, but not sure how that would happen. Lock escalation maybe? So my question is if someone could help shine some light on what this waitresource is and what might cause it.

The issue was fixed before I could look at the activity while it was happening so just have the block report to go on. I tried googling this but could not find any info on this. Any help or pointers to a resource that will help is appreciated

Best Answer

The MSDN sys.dm_tran_locks documentation shows a list of resource and resource subtypes, including those affecting database locking:

BULKOP_BACKUP_DB         Database backups with bulk operations.
BULKOP_BACKUP_LOG        Database log backups with bulk operations.
CHANGE_TRACKING_CLEANUP  Change tracking cleanup tasks.
CT_DDL                   Database and table-level change tracking DDL operations.
CONVERSATION_PRIORITY    Service Broker conversation priority operations such
                             as CREATE BROKER PRIORITY.
DDL                      Data definition language (DDL) operations with 
                             filegroup operations, such as drop.
ENCRYPTION_SCAN          TDE encryption synchronization.
PLANGUIDE                Plan guide synchronization.
RESOURCE_GOVERNOR_DDL    DDL operations for resource governor operations such 
                             as ALTER RESOURCE POOL.
SHRINK                   Database shrink operations.
STARTUP                  Used for database startup synchronization.
Related Question