Is there anyone know any feature in SQL server which can detect/log the transaction which has been blocked unusually long? My idea is:
If a transaction has run for over 30 minutes, then log the following information in SQL server log:
1. State of the transaction : Blocked, Running, Sleeping
2. Time happeing
3. Duration
4. Last SQL it run
5. Blocked by which thread and info of the thread : the last SQL it run, the state of the thread.
Possibly it would be run in SQL server agent or SQL profiler has such feature, I am not sure about it. Can anyone suggest? Thanks.
If you are using SQL 2005, refer to the usage of the dynamic management view sys.dm_tran_active_transactions in Books Online.
For SQL 2000, this article may help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271509
|||Thanks Arnie, I am using 2005 and this link is helpful:
http://msdn2.microsoft.com/en-us/library/ms191168.aspx
It provides the infomation of the Blocked Process Report Event Class object
