View Statements that lock tables or rows

• Bobby Glover

  SSCarpal Tunnel

  Points: 4143

  More actions

  March 2, 2011 at 8:59 am

  #232919

  Hi all

  I need to find a quick way of seeing what SPROCS on my reporting repository database (SQL 2005) are creating locks on tables.

  I have run sys.dm_tran_locks I can identify the table which has a lock on it but not the statement that caused it.

  I don't want any locks in my database as it is read only and only used for reporting purposes.

  Thanks in advance.

  Rob Glover.

• ChazMan

  SSCrazy

  Points: 2259

  More actions

  March 2, 2011 at 9:59 am

  #1293604

  run profiler using the locks counters. There is a default TSQL_Locks template...try that. You should get your sql in question.

  --------------------------------------------------------------------------
  When you realize you've dug yourself into a hole....Step 1...stop digging.