SQL Profiler InputBuffer Events in SQL Server

As a DBA, sometimes you need to identify the last execution happened or happening in your server. What you will exactly do?

1. Start your profiler and check it.
2. Check the process info for the server processes running.
3. In SQL Server 2008, you can opt Activity Monitor.

What else?????????

Here is another method using queries? If you got the spid then DBCC Inputbuffer command will provide you the last execution command of your user. Let's see how?????

Get the current session id

SELECT @@SPID

The output is, 51

Let's execute the below query now,

CREATE TABLE VenkatTable (Col1 int)

GO

DECLARE @i int;

SELECT @i = 0

BEGIN TRAN

SELECT @i = 0

WHILE (@i < 100000) BEGIN INSERT INTO VenkatTable VALUES (@i) SELECT @i = @i + 1 END; COMMIT TRAN;

Now, open another session or connection, and execute the command 

DBCC INPUTBUFFER (51);

 

You will get the last statement executed by the particular session user.

Usually, we will get confused on seeing the sequence of below statements in our profiler.

create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters] int, [Event Info] nvarchar(512))

insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(51)')

select [Event Info] from #tmpDBCCinputbuffer

It's nothing but the in-house or profiler query itself. The above statements were used to fetch the latest statements of your server processes.