Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 104
  • Last Modified:

get history of locks?

is it possible to get dates of when last data locks happened on select queries?
0
25112
Asked:
25112
  • 3
  • 3
  • 2
5 Solutions
 
25112Author Commented:
I checked master.syslockinfo but it does not have any date, but some binary data.

also master.dbo.syslockinfo  is bringing different count than master.sys.syslockinfo. i am assuming lock info is captured per db specific.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't since the available information it's in general a snapshot of what's going on at the moment.
The only way it's to create a database where you can keep all those information for later verification.
0
 
Scott PletcherSenior DBACommented:
The new view is sys.dm_tran_locks.  But you won't find datetime.  I suspect that would be just too much overhead to keep up with.  When SQL gets busy, locks are taken and released at a huge rate, so trying to time stamp all of them would be too damaging to performance.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
25112Author Commented:
OK, what I get is that unless i profile(r), i cant capture past locks..

then, is there any value to this query, now:
select * from master.dbo.syslockinfo a join master.dbo.sysprocesses b on a.req_spid=b.spid where object_name(a.rsc_objid) is not null
0
 
Scott PletcherSenior DBACommented:
Hmm, I don't really know.  I haven't used those obsolete views in years, so I'm not familiar with them any more.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That query only works if when you run it the lock still exists. If not, will return nothing.
Like I told you that's only work as snapshot not with stored information. For that you need to create your own monitoring tool or use one as SCOM or another one similar.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Here's an article how to create your own monitoring tool for deadlocks.
0
 
25112Author Commented:
thank you
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now