We often need to find the last running query or based on SPID need to know which query was executed. SPID is returns sessions ID of the current user process. The acronym SPID comes from the name of its earlier version, Server Process ID.
To know which sessions are running currently, run the following command:
SELECT @@SPID
GO
In our case, we got SPID 57, which means the session that is running this command has ID of 57.
Now, let us open another session and run the same command. Here we get different IDs for different sessions.
In our case, we got SPID 61. Please note here that this ID may or may not be sequential.
In session with SPID 61, we will run any query. In session with SPID 57, we will see which query was run in session with SPID 61.
Let us run a simple SELECT statement in session with SPID 61 and in session with SPID 57 run the following command.
DBCC INPUTBUFFER(61)
GO
Now, here in DBCC command we have passed the SPID of previous session; we will see the text below. The following image illustrates that we get the latest run query in our input buffer.
There are several ways to find out what is the latest run query from system table sys.sysprocesses.
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61
SELECTÂ TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO
The following image portrays that we get the latest run query in our input buffer.
Please note that in any session there may be multiple running queries, but the buffer only saves the last query and that is what we will be able to retrieve.
There is one more way to achieve the same thing – using function fn_get_sql
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61
SELECTÂ TEXT
FROM ::fn_get_sql(@sqltext)
GO
All the three methods are same but I always prefer method 2 where I have used sys.sysprocesses.
Today, we have explored a very simple topic. Let me know if you find it useful.
Reference : Pinal Dave (https://blog.sqlauthority.com)
43 Comments. Leave new
I usually don’t leave replies (i’m a little lazy for that) but man you really rock! I have read so many blogs and you have a real clear style for explain things! Thanks for share all your knowledge. :D
I found it useful!
me too
Thanks!
Is there any way to determine when a specific SPID was created (born)?
Hi there
Firstly thank you very much for this blog. It is quite useful and informative.
Is there a way to query the db and get the command sent to a process. Similar to what is done here, but instead of it returning the procedure name it returns the command sent to the procedure. I know this can be done with profiler, but I am looking at a job and profiler is not very helpful here.
Thank you for the assistance
Really your Blog helps me a lot for each and every problem i found solution your blog,thank you very much…
Thanks Balaji.
Thanks, great tip
Thanks Miguel.
Hi Pinal
I sure found it useful – it was exactly what I needed and was simplicity and clarity at its best
Thanks Olu
Thank you for your help.
When the query runs in parallel, you must choose only the first row:
DECLARE @sqltext VARBINARY(128)
SELECT TOP 1 @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 202
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO
DBCC INPUTBUFFER(XX) (First Example) absolutely does not work as advertised in SQL Server 2012
I take that back but THIS IS IMPORTANT! COMMENTED CODE IS CONSIDERED AS THE LAST RUNNING QUERY – at least in 2012
The following:
SELECT GETDATE() DANDD
GO
–DBCC INPUTBUFFER( 60)
— SELECT request_id , @@spid FROM sys.dm_exec_requests WHERE session_id = @@spid;
Returned:
Language Event 0
–DBCC INPUTBUFFER( 60)
— SELECT request_id , @@spid FROM sys.dm_exec_requests WHERE session_id = @@spid;
NOT:
Language Event 0 SELECT GETDATE() DANDD
as I would have expected!
–DBCC INPUTBUFFER( 60)
Should be:
-–DBCC INPUTBUFFER( 60)
in the previous comment
Great KB article!
First, thank you for your article. What I am trying to do though goes a step in a different direction. I will skip the long story but I am trying to find a way to have a procedure able to identify the procedure or code that called it. This is similar to how a call stack works in C#, C++ and others. Since they execute on the same SPID the dbcc buffers seem to only report the current process. What I am looking for, simply, is given a situation where ProcedureA executes ProcedureB, I need ProcedureB to log be able to identify that ProcedureA called it. (from there the rest of what I need to do is well known and trivial) Can you help?