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!

so your code should be ... select * from [some_table3] where [spid] = 100 order by [some_date] desc — junketsu, Dec 13 '18 at 14:31
No. The input buffer contains the *input buffer*, not a history of all statements ever done. If you need this, you need to setup a profiler or extended events trace -- in advance. It's not reasonable to expect the engine to track all executed statements "just because" (it would gobble up a ton of resources for connections that execute a lot of statements/sec). — Jeroen Mostert, Dec 13 '18 at 14:33
Only the last batch is returned by the DMVs or DBCC INPUTBUFFER. You'll need a trace to capture history of activity per session. — Dan Guzman, Dec 13 '18 at 14:33
Note that if you're trying to get at "all statements" because you're looking for one that bollixed things up (such as dropping things it shouldn't have), you can also get some valuable data from the [default trace](https://dba.stackexchange.com/questions/48052/what-event-information-can-i-get-by-default-from-sql-server). This still won't allow you to track humble `SELECT`s, though. — Jeroen Mostert, Dec 13 '18 at 14:36

score 1 · Answer 1 · answered Dec 13 '18 at 14:38

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.

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

1 Answers1