As SQL Server users, we often encounter situations where we need to find the last running query or determine which query was executed based on the Session ID (SPID). SPID stands for Server Process ID and it represents the unique identifier for a user session.

To identify the sessions that are currently running, we can execute the following command:

SELECT @@SPID;

Upon executing this command, we will receive the SPID of the current session. For example, if we obtain SPID 57, it means that the session executing this command has an ID of 57.

Let’s open another session and run the same command. In this new session, we will receive a different SPID. For instance, let’s say we get SPID 61. It’s important to note that these IDs may or may not be sequential.

Now, in the session with SPID 61, let’s execute a query. In the session with SPID 57, we can use the following command to determine which query was run in the session with SPID 61:

DBCC INPUTBUFFER (61);

By passing the SPID of the previous session to the DBCC command, we can retrieve the latest run query in our input buffer.

Another approach to achieve the same result is by querying the system table sys.sysprocesses. We can use the following code:

DECLARE @sqltext VARBINARY(128);
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61;
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext);

This method retrieves the latest run query from the input buffer. It’s important to note that the buffer only saves the last query, so we will only be able to retrieve the most recent one.

Alternatively, we can use the function fn_get_sql to achieve the same result:

DECLARE @sqltext VARBINARY(128);
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61;
SELECT TEXT
FROM ::fn_get_sql(@sqltext);

All three methods yield the same outcome, but personally, I prefer using method 2, which involves querying the sys.sysprocesses table.

Today, we have explored a simple yet useful topic in SQL Server. I hope you found this information helpful. If you have any questions or suggestions, please let me know.