SQLServer ロック状況を取得して強制切断する

信じられないことだけどSQLServerサーバを共有して開発をしていると
テーブルを排他ロックをかけたままにしてしまう人がいる（いた）。

相談をうけてロックする犯人の特定とロックの解除を行う作業に
使ったコマンドをまとめる。

手順は、「ロック対象を取得するクエリ」で対象を取得し「対象プロセスを中断するクエリ」を実行するだけです。
ロック長期保有しているエンジニアにお説教するのも忘れずに。対象クエリは強制切断する。

■テーブル取得クエリ

--ロック情報取得
SELECT * FROM sys.dm_tran_locks
--プロセス情報取得
SELECT * FROM sys.sysprocesses
--データベース情報取得
SELECT * FROM sys.databases
--テーブル情報取得
SELECT * FROM sys.tables

■ロック対象を取得するクエリ
状況を確認して実行ユーザを特定する。

-- 対象のデータベース
DECLARE @database AS VARCHAR(20) = 'AkairoDB'

-- ロックリソースを取得
SELECT l.resource_type
	 , l.resource_database_id --データベースID
	 , d.name 'DatabaseName' --データベース名
	 , t.name 'TableName' --テーブルID（あれば）
	 , l.request_mode --リクエストモード（共有ロック、更新ロック、排他ロック）
	 , l.request_type --lockか？
	 , l.request_status --ロックの状態。値は GRANT または WAIT 
	 , p.spid  -- SQLServer上のセッションID
	 , p.kpid --Windows上のスレッドID
	 , p.blocked -- ブロックしているセッションの ID
	 , p.hostname -- ホスト名
	 , p.status -- プロセス状態
	 , p.loginame -- ログインユーザ名
FROM sys.dm_tran_locks l
INNER JOIN sys.databases d
  ON d.database_id = l.resource_database_id
LEFT OUTER JOIN sys.tables t
  ON t.object_id = l.resource_associated_entity_id
INNER JOIN sys.sysprocesses p
  ON l.request_session_id = p.spid
WHERE d.name = @database
AND   p.spid <> @@SPID --自身を除外

■対象プロセスを中断するクエリ

DECLARE @spid AS smallint
--KILL { session ID | UOW } [ WITH STATUSONLY ]   
KILL @spid

＜参照＞

sys.sysprocesses (TRANSACT-SQL) | Microsoft Docs

KILL (Transact-SQL) | Microsoft Docs