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.
Hello,
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;