SQL Server - Find last sql statement based on SPID

14 January,2013 by Jack Vamvas

Find the last query based on a SPID can be useful . For example, a  client may be experiencing SQL performance issues  and the DBA needs to isolate the last run query.  There are three methods below  to return the query.

Before we explore the methods – you’ll need to obtain the SPID. The SPID is the session User ID.


--this returns your current session ID


--this returns list of all sessions-check the spid column




DBCC INPUTBUFFER  returns the last sql statement  issued by a client. The command requires the SPID






Obtain the sql_handle from sys.sysprocesses and use the sql_handle to obtain the sql text from the DMV sys.dm_exec_sql_text.


DECLARE @sqlhandle VARBINARY(64)
SELECT @sqlhandle = sql_handle from sys.sysprocesses where spid = 987
SELECT text FROM sys.dm_exec_sql_text(@sqlhandle)


Method 3 – SYS.FN_GET_SQL


Use sys.fn_get_sql to return SQL statement text based on a SQL handle. Microsoft intend deleting this function in an unspecified future version of SQL Server          


DECLARE @sqlhandle varbinary(64);
SELECT @sqlhandle = sql_handle 
FROM sys.dm_exec_requests 
WHERE session_id = 987 and request_id = 0;
SELECT * FROM sys.fn_get_sql(@sqlhandle);

 Read More

dm_exec_sql_text and Display text of SQL - SQL Server DBA

SQL Server - SQL open transactions and how to find

SQL Server – Current queries executing on SQL Server

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server - Find last sql statement based on SPID

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer