2

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

1

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.
  • 1
    Thanks for that info. When I check there I can see the resource_type as DATABASE, but the resource_subtype is blank. Not much else in that table that seems to give me much more information, although if the subtype had something there that would help. – Dustin Mar 8 '17 at 21:27
1

Found the issue. There were several mirrored databases that were failed over to the secondary around the same time. I had to go through a ton of block reports to find this. When the command to send the failover occurred it put a lock on the database which blocked anything from using it. There were several other things that were executing at the time that created a chain of additional blocks. I was seeing the additional blocks and not the original one that started everything.

Here is the command that was running -

USE [master];ALTER DATABASE [DBName] SET PARTNER FAILOVER

Seems this would have showed in the sys.dm_tran_locks table, but it did not. Also, had our monitoring tools been working at the time, it would have probably been easier to identify.

  • You may accept your own answer (green tick) though you won't earn any reputation points, it will show this question has an accepted answer. – Paul White Mar 15 '17 at 4:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.