SQL SERVER – Get Last Running Query Based on SPID

We often need to find the last running query or based on SPID need to know which query was executed. SPID is returns sessions ID of the current user process. The acronym SPID comes from the name of its earlier version, Server Process ID.

To know which sessions are running currently, run the following command:

SELECT @@SPID
GO

In our case, we got SPID 57, which means the session that is running this command has ID of 57.

Now, let us open another session and run the same command. Here we get different IDs for different sessions.

In our case, we got SPID 61. Please note here that this ID may or may not be sequential.

In session with SPID 61, we will run any query. In session with SPID 57, we will see which query was run in session with SPID 61.

Let us run a simple SELECT statement in session with SPID 61 and in session with SPID 57 run the following command.

DBCC INPUTBUFFER(61)
GO

Now, here in DBCC command we have passed the SPID of previous session; we will see the text below. The following image illustrates that we get the latest run query in our input buffer.


There are several ways to find out what is the latest run query from system table sys.sysprocesses.

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

The following image portrays that we get the latest run query in our input buffer.


Please note that in any session there may be multiple running queries, but the buffer only saves the last query and that is what we will be able to retrieve.

There is one more way to achieve the same thing – using function fn_get_sql

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


All the three methods are same but I always prefer method 2 where I have used sys.sysprocesses.

Today, we have explored a very simple topic. Let me know if you find it useful.

Reference : Pinal Dave (http://blog.sqlauthority.com)

About these ads

30 thoughts on “SQL SERVER – Get Last Running Query Based on SPID

  1. Note that “fn_get_sql” is going to be removed in a future version of SQL Server, and should not be used in new development.

    sys.sysprocesses is for backwards compatiibility only, and for new development BOL recommends using these views instead.
    sys.dm_exec_connections
    sys.dm_exec_sessions
    sys.dm_exec_requests instead

    You can view backward compatibility mappings here
    http://msdn.microsoft.com/en-us/library/ms187997.aspx

    • Simon this is very true and Pinal’s Code would need to be changed to the following:

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

      Unfortunately unlike sys.sysprocesses, sys.dm_exec_requests only holds sql handles for active sessions. Microsoft have advised that deprecation of sys.sysprocesses will not happen until ALL functionality can be replaced by the DMV’s, and clearly there is still some way to go….

  2. Hi,

    imagine we have a very very long running stored procedure. When I use this method, what I get? It will be the whole procedure or the current running statement from this procedure?

    Thanks.

  3. Great today i search for it and find it in your blog…
    i wonder how i miss this one well nice one i really need this today..
    Thanks.. for this

  4. Pingback: SQL SERVER – Get Query Running in Session Journey to SQL Authority with Pinal Dave

  5. This query is very useful …and I use it all the time…

    however, my question is…sometimes when the query is huge like run from an application or SSAS or something like that, even this query does not give us the COMPLETE query…

    Is there anyway to know that?

    Thanks in advance…

  6. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31 Journey to SQLAuthority

  7. What about right to execute these sys.dm views ? They are not public.
    So you need to be database admin by defaut.

    Is there a ‘public’ way to find Spid information such as the program_name that is running under a particular spid ?

  8. hello, i have sql server on my system. the software engineer executed a command. now i want to know which command he executed. he also used an exe file called sql tool.

  9. I run:
    DECLARE @sqltext VARBINARY(128)
    SELECT @sqltext = sql_handle
    FROM sys.sysprocesses
    WHERE spid = 1535
    SELECT TEXT
    FROM sys.dm_exec_sql_text(@sqltext)

    My blocking spid is 1535
    But the result I get is
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    Any suggestions to find what the actual problem query is?

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #038 | Journey to SQL Authority with Pinal Dave

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

  12. I usually don’t leave replies (i’m a little lazy for that) but man you really rock! I have read so many blogs and you have a real clear style for explain things! Thanks for share all your knowledge. :D

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s