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?

score 19 · Accepted Answer · answered Jul 28 '09 at 19:34

19

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

answered Jul 28 '09 at 19:34

Sadegh

6,654
4
34
44

2

Didn't know that. That's sweet! – Brandon Jul 28 '09 at 19:51
1

+1 What a beautiful gem! Exactly what I needed for a debugging trigger on some of my tables – Brian Jun 22 '10 at 14:46

score 4 · Answer 2 · answered Jul 28 '09 at 19:38

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.

score 1 · Answer 3 · answered Jul 28 '09 at 19:33

1

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.

answered Jul 28 '09 at 19:33

John Saunders

160,644
26
247
397

in the context we're after it would be 'most recently started' – Hardryv Jul 28 '09 at 19:35
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. – Hardryv Jul 28 '09 at 19:38

retrieve most recently executed SQL command (T-SQL)

3 Answers3