A way to track exclusive locks

  • I have a large app with over 1500 active connections - locking is becoming an issue - in an effort to track down the locks, I wrote a query which will give me the user and application causing the lock - a few of questions:

    1.  Is the query correct?

    2. How can I link in the table affected by the lock?

    3. I intend to turn this into a stored procedure - is there a way to write the results out to a file in the SP?

    declare @counter integer

    set @counter = 1

    while @counter < 30

    begin

    select a.name, c.loginame, b.type, b.mode, c.cpu, c.memusage, c.program_name

     from master..sysdatabases a

     join (select convert (smallint, req_spid) As spid,

       rsc_dbid As dbid,

       rsc_objid As ObjId,

       substring (v.name, 1, 4) As Type,

       substring (u.name, 1, 8) As Mode

      from master.dbo.syslockinfo,

       master.dbo.spt_values v,

       master.dbo.spt_values x,

       master.dbo.spt_values u

      where master.dbo.syslockinfo.rsc_type = v.number

       and v.type = 'LR'

       and master.dbo.syslockinfo.req_status = x.number

       and x.type = 'LS'

       and master.dbo.syslockinfo.req_mode + 1 = u.number

       and u.type = 'L')b on a.dbid = b.dbid

     join (select spid, blocked, waittime, cpu, physical_io,

      memusage, program_name, loginame from master..sysprocesses) c on b.spid = c.spid

      where a.dbid <> 11 and a.dbid <> 1 and a.dbid <> 2 and a.dbid <> 4 and a.dbid <> 18

      and (b.mode = 'x' or b.mode = 'ix')

      order by a.dbid

    set @counter = @counter + 1

    end

    Thanks,

    Harley

     

  • Microsoft has a a tool that does this already:

     

    http://support.microsoft.com/default.aspx?scid=kb;en-us;271509

     

    You look at the output using a ms tool called sherlok:

     

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49847

    This can be scheduled and the output is easy to follow.

  • Andrew,

    Thanks for the response and the links!

    Harley

     

     

     

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply