Thursday, January 03, 2008

How to Find Recently Executed Queries In SQL Server [SQL Server 2005]

Today we came across a situation which required to find out recently executed queries to analyse the modifications carried out on a database server.

Here is the query which we used to find out the recently executed queries along with the date and time at which they were executed

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

 

Hope this will be useful to you also.

[end of post]

5 Comments:

Jon Sayce said...

Hi,

I regularly found myself trying to remember recent queries, so I wrote an add-in to store them:

Total SQL Recall

Jon.

ron said...

hi!!
how to find recently executed queries in Sql server 2000 .

does this query work in sql server 2000...

Gopinath M said...

This query works only SQL Server 2005.
Sorry I dont know how to get the information in sql server 2000.

Nick said...

Very helpful! You saved us easily 45 minutes today.

Thank you!

Intruder said...

Gopinath .. You Query was useful for me when I executed a query in SQL Server 2005 and forgot to save it.

Thanks a lot for sharing the information.

Regards,
Madhan.