If you have ever wondered what SQL query was last executed by your users across all SQL Server databases on your server? Then don’t worry we have solution for that also.
Please
use this query:-
use msdb
SELECT dest.text as sql_Query,deqs.last_execution_time
FROM sys.dm_exec_query_stats
AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE deqs.last_execution_time between '2017-02-07 14:00'
and '2017-02-07 14:30'
and dest.text is not null
order by 2 desc
In this SQL Query you have to input the time period start datetime and end datetime between which you want to see the history of sql statements executed on the sql server.
Note:- All the statements will be deleted once you restart the server as the cache memory is volatile and get reset on restart.
sys.dm_exec_query_stats:-
Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
sys.dm_exec_sql_text:-
Returns the text of the SQL batch that is identified by the specified sql_handle.
Sign up here with your email
Conversion Conversion Emoticon Emoticon