Systems Engineering and RDBMS

Finding the last executed SQL statement for a given SPID

Posted by decipherinfosys on May 8, 2007

There are times when you would need to find out the last SQL statement that was sent from a client to a SQL Server instance. There are a couple of ways of getting that information and we will list the different options in this blog along with the mention of the versions when those options became available. For this blog post, let’s open up two different sessions in Query Analyzer (SQL 2000 users) or Management Studio connecting to a SQL 2005 instance. In the first session, fire off this SQL:

select @@spid

Make a note of the server process ID value that you received. Now, execute this SQL:

SELECT table_name, table_Type
FROM INFORMATION_SCHEMA.TABLES
WHERE table_Type = ‘BASE TABLE’
GO

Now, from the other session, fire of this SQL:

DBCC INPUTBUFFER (<spid>)

In the above command, replace <spid> with the value that you obtained from the other session. You will see the above query that you executed last from that client session. This DBCC command is available in both SQL 2000 and SQL 2005. There are other options as well to this DBCC command like the request_id (in SQL 2005) which details the exact batch to search from within the session. You can also use WITH NO_INFOMSGS to suppress the informational messages with severity between 0 and 10.

Another method to get the last executed SQL statement by a client session is to use the built-in table valued function: fn_get_sql. This was introduced in SQLServer 2000 SP3 along with couple of column additions to sysprocesses table including sql_handle column used in the query below. It is also available as a hot fix. Please follow the link below from MSFT to get more information about fn_get_sql or look up BOL.

http://support.microsoft.com/kb/325607

In order to use new table valued functions, we need to get first value of sql_handle. Following is the complete set of SQL to get the text associated with specific SPID. In our case 60 is the SPID of client connection from which we executed above mentioned query.

DECLARE @HANDLE BINARY(20)
SELECT @HANDLE = sql_handle from sys.sysprocesses where spid = 60
SELECT text FROM ::fn_get_sql(@handle)

In SQL 2005, you can also use the DMV (dynamic management view) to get this information. We had covered dynamic management views before on our blog – here.  In the presen example, the DMV that we are going to use is dm_exec_sql_text.  Here is the SQL to get the same results as above:

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

You can combine these results with other useful system level information like the lock information, the lock modes, the hostname etc. in order to further help you in your troubleshooting efforts.

About these ads

One Response to “Finding the last executed SQL statement for a given SPID”

  1. [...] Systems Engineering and RDBMS has a couple methods for finding the last executed SQL statement for a given SPID. [...]

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers

%d bloggers like this: