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.

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)

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

Related Posts

43 Comments. Leave new

  • Thanks sir, Nice article.
    When we are running big scripts at that time this information
    is very useful.

    Reply
  • 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
    https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mapping-system-tables-to-system-views-transact-sql?view=sql-server-2017

    Reply
    • 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….

      Reply
  • Dear Pinal

    I tried it and able to retrive last query which was ran Except current session

    Regards
    Jayant Das

    Reply
  • Michal Neuwirth
    July 24, 2009 12:58 pm

    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.

    Reply
  • 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

    Reply
  • Thanks Pinal. this is really helpful

    Reply
  • Nice tip, i was big help for me !!!

    thanks !

    Reply
  • Nice one.Very helpful. Thanks

    Reply
  • Great………!!!

    Reply
  • Such a great thing you explain simon . thanks a lot

    swaraj

    Reply
  • Elangovan Kanniappan
    August 26, 2010 12:33 pm

    Hello..
    I need to get Killed(old) process details.

    thanks advances.

    Elango

    Reply
  • 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…

    Reply
  • You came through as usual, Thanks!

    Reply
  • 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 ?

    Reply
  • 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.

    Reply
  • hahahah
    we are software engg to why we will let you know :)

    Reply
  • 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?

    Reply
  • Hello Sir,
    Thanks a lot, this blog is very helpful for me.

    regards
    Ashish

    Reply
  • Thanks Sir,can you tell me how to use xml in sql server 2008.


    Regards
    Ashish

    Reply
  • Paul Wichtendahl
    September 27, 2012 3:49 am

    Thank you for this. However, I am not so much interested in what as when the last executed statement was run

    Reply

Leave a Reply

Menu