檢查DB Lock及解決方式

  • 237
  • 0
  • 2020-05-05

參考網址:

  1. https://dotblogs.com.tw/AlenWu_coding_blog/2017/06/03/sql_lock_check
  2. http://terence-mak.blogspot.com/2013/10/sql-server-lock.html

透過下列語法找出疑似SESSION的spid,並KILL它

SELECT request_session_id AS spid,
    resource_type AS rt,
    resource_databASe_id AS rdb,
    (CASE resource_type 
    WHEN 'OBJECT' then object_name(resource_ASsociated_entity_id) 
    WHEN 'DATABASE' then '<db_name>' 
    ELSE 
    (SELECT object_name(object_id) FROM sys.partitions 
        WHERE hobt_id = resource_ASsociated_entity_id) END) AS objname,
    resource_description AS rd,
    request_mode AS rm,
    request_status AS rs
FROM sys.dm_tran_locks