check history of lock info

  • Hi all,

    Is there any way to check the lock history on production db besides turn on any additional trigger related to lock or running the profiler on production? Thanks a lot.

    Best regards,

    Wallace

  • What kind of lock ? Do you mean different transactional locks like shared, update locks etc ??

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (9/6/2010)


    What kind of lock ? Do you mean different transactional locks like shared, update locks etc ??

    Yes, all kinds of transactional locks like read lock, update lock, exclusive lock etc. Thanks.

  • Chan Wai Yin (9/7/2010)


    Bhuvnesh (9/6/2010)


    What kind of lock ? Do you mean different transactional locks like shared, update locks etc ??

    Yes, all kinds of transactional locks like read lock, update lock, exclusive lock etc. Thanks.

    sp_lock

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Exec sp_lock (which is deprecated, included only for backward compatibility with SQL 2000 and should not be used for new development) will tell you the current locks in the system.

    There is no record of historical locks, no log of what's been taken by what in the past. If you need that (and I'm curious why), you'll need to trace it. Be aware, those are nigh-frequency events and the trace is going to hurt the server. Short duration, minimal columns, save to a fast local drive.

    p.s. The replacement for sp_lock is a DMV

    SELECT <columns required> FROM sys.dm_tran_locks

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (9/7/2010)


    Exec sp_lock (which is deprecated, included only for backward compatibility with SQL 2000 and should not be used for new development) will tell you the current locks in the system.

    There is no record of historical locks, no log of what's been taken by what in the past. If you need that (and I'm curious why), you'll need to trace it. Be aware, those are nigh-frequency events and the trace is going to hurt the server. Short duration, minimal columns, save to a fast local drive.

    p.s. The replacement for sp_lock is a DMV

    SELECT <columns required> FROM sys.dm_tran_locks

    Thank you. Since we currently we have our replication cannot synchronize problem and the problem only happen during deletion and we suspect it may related to deadlock or other lock conflicts and therefore we want to find out how to capture the locking event at that moment.

    Thanks and regards,

    Wallace

  • For deadlocks, turn traceflag 1222 on and the deadlock graph will be written to the error log.

    What exactly do you mean by 'our replication cannot synchronize problem'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (9/7/2010)


    For deadlocks, turn traceflag 1222 on and the deadlock graph will be written to the error log.

    What exactly do you mean by 'our replication cannot synchronize problem'

    Actually we face the problem that some deletion transactions cannot replicate from server 1 to server 2 using merge replication and these deletion transactions only involve 10 to 20 records and these transactions were index seek. This problem occurs occasionally and there's no error in replication log and hence we want to find out the lock activities in that period.

    Thanks and regards,

    Wallace

  • you can try installing one of the tool like performance analysis or spotlight from Quest, and you can browse history to see what type of locks were there at any perticular point of time.

    Or

    you can try running a job with regular frequency to fetch the records of lock and put them in some tables for future analysis.

    ----------
    Ashish

Viewing 9 posts - 1 through 9 (of 9 total)

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