I have a SQL server 2005 database that keeps locking and won't release. My application cannot commit updates to the database because there are tasks that are waiting to be processed and my app keeps crashing. If I look on activity monitor, waiting tasks just keeps going up and up until I kill the process. The problem is I can see what is in activity monitor that is causing the lock but I do not have enough information it just says blocked by session ID... Is there any way by using TSQL to find out what that process is exactly and what is it doing? i.e. query for locks on db with long wait time and how to force them to release, or prevent them? Please help!!!
A:
Try sp_lock @ProcessID then pick out the exclusive locks (mode = X or IX). You can then find out the offending objects using SELECT object_name(id). The ID is obtained from the ObjId column.
Noel Abrahams
2010-10-12 09:55:45
A:
Use the Deadlock Graph event in Profiler: See Track Down Deadlocks Using SQL Server 2005 Profiler
Mitch Wheat
2010-10-12 09:58:40