none
SQLServerでロックしているSQL文をまとめて表示したい RRS feed

  • 質問

  • SQLServer Management Studioの利用状況モニタのプロセス画面で、
    ロックしている行を選択し、右クリックで詳細を選ぶとそのSQL文が参照できます。

    ですが、大量にロックが出ている場合や、緊急時などに一つずつ開いて確認できません。

    そこでsp_lockでロックしている一覧が参照できるように、
    SQL、プロシージャ等を利用してロックしているSQL文を一気に表示する方法はありますか?

    2011年9月15日 16:43

すべての返信

  • tm1995さん、こんにちは。

    私が調査に使っているクエリがあるので紹介しておきます。
    お役にたつかどうかはわかりませんが…

    --変数の定義
    declare @dbname nvarchar(20)
    declare @hostname nvarchar(20)
    declare @sql nvarchar(max)
    
    --データベース名、ホスト名の定義
    set @dbname = 'db1'
    set @hostname = 'server1'
    
    --SQLServer内のプロセス状況をリストして、DBCCコマンド化するためにカーソルを宣言
    DECLARE CURSOR01 CURSOR FOR
    select 'DBCC INPUTBUFFER(' + convert(nvarchar(10),spid) + ')' as sql
    from master.dbo.sysprocesses
    where db_name(dbid) = @dbname
    and hostname = @hostname
    and spid > 50
    
    --カーソルをオープン
    OPEN CURSOR01
    
    --対象行の取出し
    FETCH NEXT FROM CURSOR01 INTO @sql
    
    --対象行の処理
    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXECUTE sp_executesql @sql
    FETCH NEXT FROM CURSOR01 INTO @sql
    END
    
    --カーソルのクローズ
    CLOSE CURSOR01
    
    --カーソルの宣言解除
    DEALLOCATE CURSOR01
    

    • 回答の候補に設定 山本春海 2011年10月4日 7:21
    • 編集済み Kenichi UENO 2012年8月29日 3:09 Sqlをコードブロック化
    2011年9月16日 8:22
  • 回答ありがとうございます。

    上記クエリを実行したところ、確かにSQL文は出力したのですが、

    SELECT * FROM  aaa WHERE bbb=@P0 というようになります。

    ロックしている対象を明確にするため、パラメータ化された部分(@P0など)の値を見る方法はありますか?

    2011年9月17日 16:46
  • tm1995さん、こんにちは。

    クエリ内に含まれる変数に何が入っているか、ということですね
    正直なところ、私もその手法がわかりません。

    そのため、変数の内容を知りたい場合には、そのクエリ(ストアド)の処理直前で "PRINT @P0" を追加して変数内部を直接出力するように修正してデバッグしています。
    あまり力になれず申し訳ありません。
    もっと良い方法を知っている人が出てくることを祈っております。

    2011年9月20日 2:21