如何讓 sql 自動檢查Lock狀態並解鎖

參考引用
--
 身為ERP管理人員 最討厭的就是 SQL的 Lock 問題
當Lock問題一發生時 如果你運氣好是在上班時間的話就可以直接處理

但是如果你夠倒楣的話 在下班或假日的時候發生你就 OX了
我有ㄧ次印象最深刻的就是凌晨1點的時候被告知發生 Lock狀況發生……….

為了解決這個問題 我自己寫了一個自動偵測Lock 問題並自動解鎖的功能
從此就不用那麼命苦了……..

現有設備:
MS-SQL2000 的伺服器 主要MS-SQL主機為 ERPDB

第一步 新增預存程序
在ERPDB SQL Server 下的 資料庫 à master à 預存程序 頁面下 按右鍵 選新增預存程序
並將 下列SQL貼上 檢查語法沒有問題後按確定

create PROCEDURE auto_checkblocks AS

if exists ( select * from master..sysprocesses where blocked <> 0 )
begin
     --Select '有Black現象'
    Create Table #TestTale (spid varchar(5),cmd varchar(20))
     -- '以下是引起阻塞的语句' --
    Insert #TestTale
    select distinct spid = convert( varchar(5), a.spid, 4 ), cmd = convert( varchar(16), a.cmd )
    from master..sysprocesses a
    where spid in ( select blocked from master..sysprocesses ) and blocked = 0
    order by spid

     -- '以下是被阻塞的等待执行的语句' --
    Insert #TestTale
    select distinct spid = convert( varchar(5),a.spid, 4 ), cmd = convert( varchar(16), a.cmd )
    from master..sysprocesses a
    where blocked <> 0
    order by spid

   DECLARE MY_CURSOR Cursor FOR
       Select spid from #TestTale Where (cmd = 'AWAITING COMMAND' or cmd = 'SELECT' )
   Open MY_CURSOR
   DECLARE @spid varchar(5)
   Fetch NEXT FROM MY_CURSOR INTO @spid
       While (@@FETCH_STATUS <> -1)
       BEGIN
          Select  ( 'kill ' + @spid )
           exec ( 'kill ' + @spid )
           FETCH NEXT FROM MY_CURSOR INTO @spid
       END
   CLOSE MY_CURSOR
   DEALLOCATE MY_CURSOR

   Drop Table #TestTale
end
GO

預存程序 : 該程序會自動找出目前 MS-SQL 有沒有 Lock的狀況發生 如果發生了以後 自動判斷刪除 造成 Lock狀況的使用者

使用方式 : Exec auto_checkblocks
說明        : 該程序會自動找出目前 MS-SQL 有沒有 Lock的狀況發生
         如果有的話 會將使用狀態為 'AWAITING COMMAND' or 'SELECT' 的使用者踢走 解除 Lock狀態