Monday, January 28, 2013

Get Sql Server History queries


When you are using Sql Server Management studio, some times you might forget to save a query you have just created and accidentally close the query window. In that case you can use the Sql Server cache to to the history of queries.

SELECT d.plan_handle ,
d.sql_handle ,
e.text,
d.last_execution_time,
dbname = db_name(e.dbid)
FROM sys.dm_exec_query_stats d
CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS e 

The data in sys.dm_exec_query_stats is reset every time SQL Server is restarted Or the plan cache was flushed due to memory pressure.

No comments:
Write comments
Recommended Posts × +