I was recently looking for syntax where I needed a query running in any particular session. I always remembered the syntax and ha d actually written it down before, but somehow it was not coming to mind quickly this time. I searched online and I ended up on my own article written last year SQL SERVER – Get Last Running Query Based on SPID. I felt that I am getting old because I forgot this really simple syntax.
This post is a refresher to me. I knew it was something so familiar since I have used this syntax so many times during my performance tuning project.
Run the following query to find out what the latest query that was executed in the session. There are various methods mentioned in my earlier post, so here I am picking only the one that I use most of the time.
Please use Shortcut – CTRL+T or enable “result to text” in the resultset to get formatted output.
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = (YourSessionID)
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO
You can find a range of active session IDs in your system by running system stored procedure sp_who2.
The following is the resultset where I have selected the session id that is the same as from where I am running above statement.
Additionally, you can use following T-SQL script as well.
SELECT TEXT
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)
WHERE session_id = (yoursessionID)
GO
Reference: Pinal Dave (https://blog.sqlauthority.com)
