SQL SERVER – Get Query Running in Session

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.

SQL SERVER – Get Query Running in Session sysprocesses

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)

, ,
Previous Post
SQL SERVER – Microsoft SQL Server 2008 Service Pack 2 Download
Next Post
SQLAuthority News – SQL Server Seminar at Colombo Full – Hyderabad Few Seats Available

Related Posts

6 Comments. Leave new

  • I guess we can use @@SPID instead of ‘YourSessionID’.

    Reply
  • 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

    Reply
  • Subscribe to Exclusive Newsletter bhai y to nai chal please check and do proper work hire me

    Reply
  • Arun Sir super job great work you are such a talent person

    Reply
  • 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

    Reply
  • select * from sys.dm_exec_sql_text((select sql_handle from sys.sysprocesses where spid = “SPID HERE”))

    Reply

Leave a Reply

Menu