Creation time of a lock in SQL Server 2008

So I'm trying to monitor the longest living lock in my database. The idea is that if a lock has been held for a certain amount of time, I will receive a warning in my application.

But for the l开发者_运维百科ife of I can't find the creation time of the locks.

I have used:

exec msdb..sp_lock
exec msdb..sp_who2
SELECT * FROM sys.dm_tran_locks
select * from sys.syslockinfo
select cmd, * from sys.sysprocesses where blocked > 0

But none of these seem to have the information I need.

Any ideas?

G

A long running transaction makes more sense which you can do by looking at database_transaction_begin_time column in sys.dm_tran_database_transactions

I've never known anyone to try and monitor locks... which is possibly why there is no start date/time information for them...

The Lock:Acquired EventClass in SQL Profiler has StartTime and EndTime. You might want to check that