Skip to main content

How to find recent executed query in SQL Server

Sometimes we do R&D on the query and try to found the solution of any problem and due to some reason you shut down your system without saving the query and next you need to recover your R&D query, so using the following query you can recover your executed query.


sys.dm_exec_query_stats and sys.dm_exec_sql_text catalog the view records for all currently executed query using the following query we can find the recently executed query.



SELECT dest.TEXT AS [MyQuery], a.last_execution_time AS [DateTime]
FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS dest
WHERE  a.last_execution_time>dateadd(day,-1,getdate())
ORDER BY a.last_execution_time DESC


For live example run the following query and after that execute above query to find the recently executed query.


CREATE TABLE EMP
(
     ID INT,
     NAME VARCHAR(100),
     ADDRESS VARCHAR(1000),
     DOB VARCHAR(20)
)

INSERT INTO EMP(ID,NAME,ADDRESS,DOB)
VALUES(1,'Ashish','Delhi','10-12-2012')

SELECT * FROM EMP


 
Result