Using DBCC INPUTBUFFER

This command shows the last statement executed for a particular SPID. This statement is mostly used for troubleshooting purposes to determine the exact command a particular SPID is running. You must be a member of the sys admin fixed server role, or have VIEW SERVER STATE permission (if SQL 2005+). As for a real world use, I will mostly use this command after using sp_who2 to find out which SPID is taking a lot of resources. To execute, simply replace the SPID 55 below with the one you want to spy on. DBCC INPUTBUFFER(55) And here are the results: The results you are looking for are displayed in the third column above. This shows the last statement that has been or is currently being executed by the SPID. Another alternative to the DBCC INPUTBUFFER is to use the dm_exec_requests DMV. This query will show the currently executing statement for a SPID: SELECT     SQLStatement       =         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         )         FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt WHERE er.session_id = 54 This […]

Continue reading ...

Featured Articles

 Site Author