DBCC INPUTBUFFER (Transact-SQL)

session_id

Is the session ID associated with each active primary connection.

request_id

Is the exact request (batch) to search for within the current session.

The following query returns request_id:

Copy

SELECT request_id 
FROM sys.dm_exec_requests 
WHERE session_id = @@spid

WITH

Enables options to be specified.

NO_INFOMSGS

Suppresses all informational messages that have severity levels from 0 through 10.

DBCC INPUTBUFFER returns a rowset with the following columns.

For example, DBCC INPUTBUFFER returns the following result set when the last event in the buffer is DBCC INPUTBUFFER(11).

EventType      Parameters EventInfo             
-------------- ---------- --------------------- 
Language Event 0          DBCC INPUTBUFFER (11)

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Requires one of the following:

• User must be a member of the sysadmin fixed server role.
  
• User must have VIEW SERVER STATE permission.
  
• session_id must be the same as the session ID on which the command is being run. To determine the session ID execute the following query:
  
  Copy

  SELECT @@spid
  

The following example runs DBCC INPUTBUFFER on a second connection while a long transaction is running on a previous connection.

CREATE TABLE T1 (Col1 int, Col2 char(3));
GO
DECLARE @i int;
SELECT @i = 0
BEGIN TRAN
SELECT @i = 0
WHILE (@i < 100000)
BEGIN
INSERT INTO T1 VALUES (@i, CAST(@i AS char(3)))
SELECT @i = @i + 1
END;
COMMIT TRAN;
--Start new connection #2.
DBCC INPUTBUFFER (52); 

Reference

Help and Information