I'm using a PHP web application to interact with a database and need to catch an erroneous query. It's dynamically generated SQL and I don't know 100% of the code nor did I write it so I need to see what it's doing.

Using SQL Server Management Studio 2008 I've found it amazingly hard to find the last n executed queries on the database. I've searched up a storm but all solutions I've seen require "sql_handle" which is a feature not availible in my database's compatibility level, and to boot all solutions I've seen don't appear to catch dynamic queries, which this web application uses.

I was using this query:

    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
      ( (CASE WHEN statement_end_offset = -1
ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %'
ORDER BY last_execution_time DESC

But it unacceptably requires changing sp_dbcmptlevel in SQL Server 2005 (the search requires comptlevel at 90, I need it at 80), and in a recent case doesn't even return any dynamic queries run on the database; it's only returning the actions of stored procedures.

A similar solution with the same problems of compatability and incomplete results is:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

This one returns a very small and very odd subset of queries, including the most recent query (usually itself) and a bunch of create procedure statements.

Is there any feature of SSMS or system database query that will allow me to view or capture recent queries? If I have to capture and save the query strings somehow that's acceptable as this is only in a test system.


SQL Server Profiler was designed to do this.

With it you can:

  • capture queries as they are executed on the target instance
  • capture other instance and database events like deadlocks, logins, and errors
  • filter captured queries by text in the query, login name, database name, and so forth
  • Perfect, catches a bit more info than I need but I'm tinkering with the filters.
    – Ben Brocka
    Sep 16 '11 at 21:14
  • 1
    @SirTapTap - You can select the combination of events, columns, and column filters that gives you just what you need. When you've done that, be sure to save it as a template so you can reuse that configuration in future Profiler sessions. Sep 16 '11 at 21:19

If you get the SPID that is running the queries, this will give you the last statement run:


SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 85 --replace this with your SPID

FROM sys.dm_exec_sql_text(@sqltext)
  • Helpful but is there any way to get more than one statement from this? Unfortunately the operation I'm analyzing is a large series.
    – Ben Brocka
    Sep 16 '11 at 21:15
  • Nope, as you noted above the Profiler is better for that sort of thing. We found the above query useful because we had one column that was being changed and implemented a temporary trigger to write the results of that query to a table because profiling all of the possible databases (and not knowing when or where the data could be changed) wasn't feasible.
    – Wil
    Sep 16 '11 at 21:23
  • You could configure Profiler via column filters to capture just the queries that include the column name in question, just on the databases in question. Sep 16 '11 at 21:29
  • @Nick, granted yes. 150+ databases = no bueno though :)
    – Wil
    Sep 16 '11 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.