Technical Article

Check Locks

,

This script allows the capture of the locks to a table. After the capture, you can see the commands and the moment of the lock.

--Verifica e Loga locks 
set nocount on
declare @x varchar(255)
declare @maxSpid int

while (1=1)
       
 if exists (select P.spid from master..sysprocesses P,master..syslocks L where L.spid=P.spid) 
    begin
      select @maxSPID = P.spid from master..sysprocesses P,master..syslocks L where L.spid=P.spid             

      Insert into veLock
      select convert(varchar(20),getdate())+"--" +convert(varchar(20),getdate(),114)+" - Processo "+convert(varchar(3), L.spid)+
     "dbcc inputbuffer("+convert(varchar(3),@maxSpid)+")"+
             "(\\"+convert(varchar(30),hostname)+"\"+convert(varchar(30),program_name)+") bloqueou tipo "+convert(varchar(3),L.type)+
             " a tabela "+D.name+".."+T.name
             from master..syslocks L, 
                  sysobjects T, 
                  master..sysdatabases D, 
                  master..sysprocesses P
              where L.dbid=D.dbid
                    and D.dbid=db_id()
                    and L.spid=P.spid
                    and L.id=T.id
--    and T.id <> 514100872
         
waitfor delay "00:00:20"
       end

set nocount off

Rate

1 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (1)

You rated this post out of 5. Change rating