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)
6 Comments. Leave new
I guess we can use @@SPID instead of ‘YourSessionID’.
SELECT
xed.value(‘@timestamp’, ‘datetime’) as Creation_Date,
xed.query(‘.’) AS Extend_Event
FROM
(
SELECT CAST([target_data] AS XML) AS Target_Data
FROM sys.dm_xe_session_targets AS xt
INNER JOIN sys.dm_xe_sessions AS xs
ON xs.address = xt.event_session_address
WHERE xs.name = N’system_health’
AND xt.target_name = N’ring_buffer’
) AS XML_Data
CROSS APPLY Target_Data.nodes(‘RingBufferTarget/event[@name=”xml_deadlock_report”]’) AS XEventData(xed)
ORDER BY Creation_Date DESC
Written a query & its gives me the deadlocks but i also want the sql text
Subscribe to Exclusive Newsletter bhai y to nai chal please check and do proper work hire me
Arun Sir super job great work you are such a talent person
i have run a trace and i have found a deadlock victim through deadlock graph as spid 60 now whats the next step i was running update command in spid 61 and delete command in spid 62
select * from sys.dm_exec_sql_text((select sql_handle from sys.sysprocesses where spid = “SPID HERE”))