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
Thanks sir, Nice article.
When we are running big scripts at that time this information
is very useful.
Note that “fn_get_sql” is going to be removed in a future version of SQL Server, and should not be used in new development.
sys.sysprocesses is for backwards compatiibility only, and for new development BOL recommends using these views instead.
sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_requests instead
You can view backward compatibility mappings here
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mapping-system-tables-to-system-views-transact-sql?view=sql-server-2017
Simon this is very true and Pinal’s Code would need to be changed to the following:
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.dm_exec_requests
WHERE session_id = 61
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext);
Unfortunately unlike sys.sysprocesses, sys.dm_exec_requests only holds sql handles for active sessions. Microsoft have advised that deprecation of sys.sysprocesses will not happen until ALL functionality can be replaced by the DMV’s, and clearly there is still some way to go….
Dear Pinal
I tried it and able to retrive last query which was ran Except current session
Regards
Jayant Das
Hi,
imagine we have a very very long running stored procedure. When I use this method, what I get? It will be the whole procedure or the current running statement from this procedure?
Thanks.
Great today i search for it and find it in your blog…
i wonder how i miss this one well nice one i really need this today..
Thanks.. for this
Thanks Pinal. this is really helpful
Nice tip, i was big help for me !!!
thanks !
Nice one.Very helpful. Thanks
Great………!!!
Such a great thing you explain simon . thanks a lot
swaraj
Hello..
I need to get Killed(old) process details.
thanks advances.
Elango
This query is very useful …and I use it all the time…
however, my question is…sometimes when the query is huge like run from an application or SSAS or something like that, even this query does not give us the COMPLETE query…
Is there anyway to know that?
Thanks in advance…
You came through as usual, Thanks!
What about right to execute these sys.dm views ? They are not public.
So you need to be database admin by defaut.
Is there a ‘public’ way to find Spid information such as the program_name that is running under a particular spid ?
hello, i have sql server on my system. the software engineer executed a command. now i want to know which command he executed. he also used an exe file called sql tool.
hahahah
we are software engg to why we will let you know :)
I run:
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 1535
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
My blocking spid is 1535
But the result I get is
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Any suggestions to find what the actual problem query is?
Hello Sir,
Thanks a lot, this blog is very helpful for me.
regards
Ashish
Thanks Sir,can you tell me how to use xml in sql server 2008.
—
Regards
Ashish
Thank you for this. However, I am not so much interested in what as when the last executed statement was run