retrieve most recently executed SQL command (T-SQL)

Question

One of my developers working on a trigger-based logging facility in SQL Server 2008 asked me if there was a command to retrieve the most recently executed SQL command within T-SQL. I thought there was a system stored procedure for just such a function, but it's possible I'm thinking of another product from a prior decade... online searches yielded us no results.

Does anyone have information on anything of the sort?

Layer

Answer 1

sure try this :

SELECT
DMExQryStats.last_execution_time AS [Executed At],
DMExSQLTxt.text AS [Query]
FROM
sys.dm_exec_query_stats AS DMExQryStats
CROSS APPLY
sys.dm_exec_sql_text(DMExQryStats.sql_handle) AS DMExSQLTxt
ORDER BY
DMExQryStats.last_execution_time DESC

it will returns recently executed queries along with the date and time at which they were executed

Balmacaan

Didn't know that. That's sweet! – Warbler

+1 What a beautiful gem! Exactly what I needed for a debugging trigger on some of my tables – Dierolf

Answer 2

Well, the procedure that retrieves the most current SQL batch can safely return itself :)

On a serious note, you can look at sys.dm_exec_query_stats and sys.dm_exec_procedure_stats to see when a plan was last time executed, based on the last_execution_time column. But note that the method is not reliable because it does not account for plans that were evicted from the cache after execution.

Circuity

Answer 3

What does "most recent" mean in the context of a multi-core machine?

Also, does he mean the most recently started, or the most recently finished?

Finally, he should just open SSMS and look at Activity Monitor.

Unattached

in the context we're after it would be 'most recently started' – Layer

the SSMS will surely be useful at allowing us to review the commands, but what we're really seeking is a programmatic solution, hence the specific quest for a system-stored-procedure. – Layer