Get more than 10 minutes of SQL script regardless of whether it caused the lock or not
SELECT [Spid] = session_id
Ecid
[Database] = DB_NAME (sp.dbid)
[User] = nt_username
[Status] = er.status
[Wait] = wait_type
[Individual Query] = SUBSTRING (qt.text
Er.statement_start_offset / 2
(CASE WHEN er.statement_end_offset =-1
THEN LEN (CONVERT (NVARCHAR (MAX), qt.text))
* 2
ELSE er.statement_end_offset
END-er.statement_start_offset)
/ 2)
[Parent Query] = qt.text
Program = program_name
Hostname
Nt_domain
Start_time, datediff (MINUTE,start_time,GETDATE ()) duration_time_seconds
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) AS qt
WHERE session_id > 50-- Ignore system spids.
AND session_id NOT IN (@ @ SPID)-Ignore this current statement.
And datediff (MINUTE,start_time,GETDATE ()) > 10
ORDER BY 1
