SQL Server database keeps locking

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 tha开发者_JS百科t 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 database with long wait time and how to force them to release, or prevent them?

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.

Use the Deadlock Graph event in Profiler: See Track Down Deadlocks Using SQL Server 2005 Profiler