T-SQL how to get more than last query executed by SPID from the cache (DBCC INPUTBUFFER, sys.sysprocesses)

Question

I have this scenario:

SPID = 100 (A SSMS tab for example)


SELECT TOP 1 * FROM SOME_TABLE
GO
SELECT TOP 1 * FROM SOME_TABLE2
GO
SELECT TOP 1 * FROM SOME_TABLE3

When I run (DBCC INPUTBUFFER, sys.sysprocesses), I got only the last query executed:

  SELECT TOP 1 * FROM SOME_TABLE3.

I need to get all queries from that session (in this case spid 100), not only the last. Is there a way to do this?

I'm searching for a way to do this using TSQL, get a trace sql-server Profiler is not a option.

Thanks!

Answer 1

You need to capture the queries using Extended Events or Profiler. It will be better to use XE. Create a session like this one:

CREATE EVENT SESSION [Capture_Queries]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
  ACTION 
  (
          sqlserver.sql_text
  )
  WHERE 
  (
          session_id = 100
  )
)
ADD TARGET package0.event_file
(
    SET filename = 'D:\CaptureQueries.xel',
        max_file_size = 5,
        max_rollover_files = 1
)

After that you can start and stop it with these commands:

ALTER EVENT SESSION [Capture_Queries] ON SERVER STATE = START
ALTER EVENT SESSION [Capture_Queries] ON SERVER STATE = STOP

Start the session, execute the queries and then stop it. You can see the captured queries in SSMS using Management \ Extended Events \ Sessions \ Capture_Queries node in Object Explorer - there is a package0.event_file node under the session. Double click it to see the collected data.