SQLTeam.com | Weblogs | Forums

Tracing Lock in SQL 2008 R2




I have been struggling to solve a problem or show that the problem is not with SQL. A application kicks off an process. I am not sure what process that is. After a few minutes a deadlock happens on a table. I have few scripts that shows me the following information. HOSTIP, Block in Session ID, Blocking User, BlockedUser ens.

The blocked user is always the same user that keeps locking the database. Is there any other method how I can find out what is locking the table. My scripts do provide me with information but not 100%. I need to know to resolve the locking table issue.


After reading the information at http://www.mssqltips.com/sqlservertip/1978/understanding-sql-server-blocking/
I came up with: (I hope it helps) SELECT CAST(DB_NAME(DTL.resource_database_id) AS VARCHAR(60)) AS DatabaseName ,sp_blocked.cmd AS Command ,DOWT.wait_type AS WaitType ,DTL.request_status AS RequestStatus ,DOWT.wait_duration_ms AS Wait_Time ,DOWT.session_id AS BlockedSessionID ,sp_blocked.hostname AS BlockedHostName ,sp_blocked.loginame AS BlockedUser ,sp_blocked.program_name AS BlockedProgram ,(SELECT [text] AS [text()] -- Query gets text as XML for the blocked query FROM sys.dm_exec_sql_text(sp_blocked.sql_handle) FOR XML PATH(''), TYPE) AS BlockedCommand ,DOWT.blocking_session_id AS BlockingSessionID ,sp_blocking.hostname AS BlockingHostName ,sp_blocking.loginame AS BlockingUser ,sp_blocking.program_name AS BlockingProgram ,(SELECT [text] AS [text()] -- Query gets text as XML for the blocking query FROM sys.dm_exec_sql_text(sp_blocking.sql_handle) FOR XML PATH(''), TYPE) AS BlockingCommand FROM sys.dm_tran_locks DTL INNER JOIN sys.dm_os_waiting_tasks DOWT ON DTL.lock_owner_address = DOWT.resource_address INNER JOIN sys.sysprocesses sp_blocked ON DOWT.session_id = sp_blocked.spid INNER JOIN sys.sysprocesses sp_blocking ON DOWT.blocking_session_id = sp_blocking.spid;



I will give this script a test. Thank you