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.

Solarwinds

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.

SQL SERVER - Get Last Running Query Based on SPID spid1

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.

SQL SERVER - Get Last Running Query Based on SPID spid2

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

SQL SERVER - Get Last Running Query Based on SPID spid3

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 (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQLAuthority News – Whitepaper – Using the Resource Governor
Next Post
SQLAuthority News – Authors Visit – DelhiBuzz TechEd on July 11, 2009

Related Posts

41 Comments. Leave new

  • 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

    Reply
  • I found it useful!

    Reply
  • Is there any way to determine when a specific SPID was created (born)?

    Reply
  • Hi there

    Firstly thank you very much for this blog. It is quite useful and informative.

    Is there a way to query the db and get the command sent to a process. Similar to what is done here, but instead of it returning the procedure name it returns the command sent to the procedure. I know this can be done with profiler, but I am looking at a job and profiler is not very helpful here.

    Thank you for the assistance

    Reply
  • Really your Blog helps me a lot for each and every problem i found solution your blog,thank you very much…

    Reply
  • Thanks, great tip

    Reply
  • Hi Pinal

    I sure found it useful – it was exactly what I needed and was simplicity and clarity at its best

    Reply
  • David Lozano Lucas
    September 1, 2017 7:14 pm

    Thank you for your help.
    When the query runs in parallel, you must choose only the first row:

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

    Reply
  • DBCC INPUTBUFFER(XX) (First Example) absolutely does not work as advertised in SQL Server 2012

    Reply
    • I take that back but THIS IS IMPORTANT! COMMENTED CODE IS CONSIDERED AS THE LAST RUNNING QUERY – at least in 2012
      The following:

      SELECT GETDATE() DANDD
      GO

      –DBCC INPUTBUFFER( 60)
      — SELECT request_id , @@spid FROM sys.dm_exec_requests WHERE session_id = @@spid;

      Returned:
      Language Event 0
      –DBCC INPUTBUFFER( 60)
      — SELECT request_id , @@spid FROM sys.dm_exec_requests WHERE session_id = @@spid;

      NOT:
      Language Event 0 SELECT GETDATE() DANDD

      as I would have expected!

      Reply
      • –DBCC INPUTBUFFER( 60)
        Should be:
        -–DBCC INPUTBUFFER( 60)
        in the previous comment

Leave a Reply

Menu