Sql-server – Blocking process inputbuf is empty

blockingsql server

I was trying to capture blocking query using blocked process report past 1 hour as this link
https://www.mssqltips.com/sqlservertip/4480/find-blocking-processes-using-sql-server-profiler/

I can get the blocked process query using sqlhandle but for blocking process query i cannot get it because no sqlhandle and inputbuf doesnt have any SQL statement.

enter image description here

Any idea? This is first time i m using this blocked process report.

Best Answer

I use this query to monitor all running SPID's and the column Blocked shows you the blocking session ID. Try it

SELECT s.STATUS
    ,db_name(r.database_id) AS DB
    ,s.session_id AS SPID
    ,r.blocking_session_id AS blocked
    ,s.login_name AS userN
    ,s.host_name AS pc
    ,s.program_name AS app
    ,t.TEXT
    ,spu.internal_objects_alloc_page_count AS Pages_Allocated
    ,s.cpu_time AS CPU_T
    ,s.memory_usage AS Memory
    ,s.total_scheduled_time AS Tot_Sch_T
    ,s.total_elapsed_time AS Tot_Ela_T
    ,s.reads
    ,s.writes
    ,s.logical_reads AS Log_Read
    ,s.row_count
    ,s.last_request_start_time AS Last_Strt_T
    ,s.last_request_end_time AS End_T
    ,r.open_transaction_count AS OPENTRAN
    ,CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, s.last_request_start_time, s.last_request_end_time), 0), 108) AS TOTAL_TIME
    ,r.percent_complete
    ,dateadd(second, estimated_completion_time / 1000, getdate()) AS estimated_completion_time
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
LEFT JOIN sys.dm_db_session_space_usage spu ON s.session_id = spu.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id > 50
    AND s.STATUS LIKE 'RUNNING'
ORDER BY s.total_elapsed_time DESC
Related Question