Need an extended event trace definition for capturing lock info

• vsamantha35

  SSChampion

  Points: 11529

  More actions

  May 24, 2018 at 4:15 am

  #341538

  Hi All,

  Can someone help in creating an extended event  to lock information of the currently running spids. Need an extended event session which captures below info for a specific <<database>>. I wanted to check what kind of locks are being acquire on an object.

  spid,
  dbid
  dbname
  procedurename
  sqlstmt started
  sqlstmt compelted
  objectname
  locks applied
  any attentions
  hostname
  client app
  batchstarttime
  batchendtime

  Suppose, if I want to execute below stored procedure. I want to see all the sql stmts along with lock info. How to do that???

  create procedure usp_p1
  as
  begin
  begin try
    select * From t1 where c1 > 2 and c1 < 10;

       insert into t1 values(4,'smith',9000)

       update t1 set c3 = c3+3000 where c1 in (101,102,103);

       delete from t1 where c1 in (1001,1002,1003);

       select * From t1;

  end try
  begin catch
    select ERROR_NUMBER();
       if @@TRANCOUNT>0
        rollback;
  end catch
  end

  Thanks,

  Sam

• Jack Corbett

  SSC Guru

  Points: 184393

  More actions

  May 25, 2018 at 9:03 am

  #1991996

  You can add teh Lock_acquired event to your trace, but that is going to be A TON of information because everything (unless using snapshot isolation) takes some kind of lock.  Another option is to download sp_WhoIsActive and use the @get_locks = 1 parameter and run sp_WhoIsActive as your process is running.  This will return an XML column with all locks held at the time that you run sp_WhoIsActive.  This might be a little more lightweight than an XE session collecting all locks.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question