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.

[cc lang=”sql”]
DBCC INPUTBUFFER(55)
[/cc]
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:

[cc lang=”sql”]
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
[/cc]

This DMV will only work on SQL 2005+ and you also need the VIEW SERVER STATE permission as a minimum. The one advantage DBCC INPUTBUFFER has over this, is the DMV will only show the currently executing request and not the previously executed request so you need to be a little quicker to catch it. The above DMV is also embedded in the sp_who3 procedure.