Jun 8, 2017

How to check which locks are held on a table in MS SQL (MS SQL에서 어떤 테이블이 Lock을 걸고 있는지 확인 하는 쿼리)

SELECT
 T1.RESOURCE_TYPE,
 T1.RESOURCE_DATABASE_ID,
 T1.RESOURCE_ASSOCIATED_ENTITY_ID,
 T1.REQUEST_MODE,
 T1.REQUEST_SESSION_ID,
 T2.BLOCKING_SESSION_ID,
 O1.NAME 'OBJECT NAME',
 O1.TYPE_DESC 'OBJECT DESCR',
 P1.PARTITION_ID 'PARTITION ID',
 P1.ROWS 'PARTITION/PAGE ROWS',
 A1.TYPE_DESC 'INDEX DESCR',
 A1.CONTAINER_ID 'INDEX/PAGE CONTAINER_ID'
FROM SYS.DM_TRAN_LOCKS AS T1
 INNER JOIN SYS.DM_OS_WAITING_TASKS AS T2
   ON T1.LOCK_OWNER_ADDRESS = T2.RESOURCE_ADDRESS
 LEFT OUTER JOIN SYS.OBJECTS O1
   ON O1.OBJECT_ID = T1.RESOURCE_ASSOCIATED_ENTITY_ID
 LEFT OUTER JOIN SYS.PARTITIONS P1
   ON P1.HOBT_ID = T1.RESOURCE_ASSOCIATED_ENTITY_ID
 LEFT OUTER JOIN SYS.ALLOCATION_UNITS A1
   ON A1.ALLOCATION_UNIT_ID = T1.RESOURCE_ASSOCIATED_ENTITY_ID

No comments:

Post a Comment