Sqlserver-dba.com

Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
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.

Try:

--this returns your current session ID

SELECT @@SPID

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

 

 Method 1 - DBCC INPUTBUFFER

-----------------------------

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

 

DBCC INPUTBUFFER (SPID)

 

Method 2 - SYS.SYSPROCESSES and SYS.DM_EXEC_SQL_TEXT

-----------------------------------

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)

******** *******

Enjoy this post? Enter your email address for updates on new posts:

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment


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