Is there any way of getting the SQL that is being run that is causing a locking at any one given time?
I'm aware of sp_who2, sys.dm_tran_locks and sys.sysprocesses to find out who is being locked and by what. But is there anything in SQL that stores the query so some sort of trigger can be build to record it?
I know the obvious answer is the SQL profiler but that will only work during the locking not after its cleared
But i'mm looking to automate really, so a trigger will put any locking instances into a table along with what was being locked and by what if that make sense? Could this be done using the DBCC inputbuffer/outputbuffer?
quote:Originally posted by visakh16
what you could do is to note the process that causes blocking using sp_who2 and then use
DBCC INPUTBUFFER (< the blocking process_id here >)