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 dbo.T1 (Col1 int, Col2 char(3));
GO
DECLARE @i int = 0;
BEGIN TRAN
SET @i = 0;
WHILE (@i < 100000)
BEGIN
INSERT INTO dbo.T1 VALUES (@i, CAST(@i AS char(3)));
SET @i += 1;
END;
COMMIT TRAN;
--Start new connection #2.
DBCC INPUTBUFFER (52);

Reference