check history of lock info

Question

Post reply

check history of lock info

Chan Wai Yin

SSCommitted

Points: 1883
More actions
September 6, 2010 at 10:34 pm

#140412

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

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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;-)

Chan Wai Yin SSCommitted Points: 1883 More actions · Answer 2

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.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

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;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

Chan Wai Yin SSCommitted Points: 1883 More actions · Answer 5

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

Chan Wai Yin SSCommitted Points: 1883 More actions · Answer 7

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

crazy4sql SSCoach Points: 19590 More actions · Answer 8

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