SQL SERVER – Hint on DBCC INPUTBUFFER() Command

Question: What is DBCC INPUTBUFFER() Command?

Question: What is the use of DBCC INPUTBUFFER() Command?

Question: What are the permission needed to use this DBCC command?

Use DBCC INPUTBUFFER (spid).This will show the last 128 characters of the last T-SQL statement executed from connection referred through spid.

 This way We can identify the stored procedure or application module that caused blocking.

Syntax:

DBCC INPUTBUFFER ( session_id [ , request_id ])

[WITH NO_INFOMSGS ]

Example:

-- select database

Use AdventureWorks

Go

-- Cearte One table for testing purpose

CREATE TABLE dbo.Testtable2 (number int);

GO

DECLARE @i int = 0;

BEGIN TRAN

SET @i = 0;

WHILE (@i < 100000)

BEGIN

INSERT INTO dbo.Testtable2 VALUES (@i);

SET @i += 1;

END;

COMMIT TRAN;

-- Now Disconnect this connection OR open new Query window and Connect again and Run

 DBCC INPUTBUFFER (54);

 --Result:

EventType      Parameters     EventInfo

-------------- ---------- ---------------------------------------------

Language Event    0           DECLARE @i int = 0;

BEGIN TRAN

SET @i = 0;

WHILE (@i < 100000)

BEGIN

INSERT INTO dbo.Testtable2 VALUES (@i);

SET @i += 1;

END;

COMMIT TRAN;

(1 row(s) affected)

Event Type means: what type of Event has been occurred

Parameters means: 0 means Text and 1-n means Parameters

Event Info means: Last Script or Query.

Here 54 is SPID. You can get SPID using following Query

Select @@SPID

Permission & Restriction:

1-       User must be a member of the sysadmin fixed server role.

2-       User must have VIEW SERVER STATE permission.

3-       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: Select @@SPID