Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Bradley Schacht

Bradley Schacht is a Data Platform Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Admin". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as SQLServerCentral.com and an active member of the Jacksonville SQL Server User Group (JSSUG).

Find Recently Run Queries

If you have ever closed a query window by accident only to wish you could get back your work an instant later you may be in luck.  If you ran the query in question there is hope for you.  The query below will return queries that have been run for you including the time and query text.  I haven’t figured up a way to get the user that executed the query just yet.  So if you have that tidbit to add to this so you can filter a little easier please let us know.

You can narrow the list by adding a where clause and searching for some text you know was in the query or looking around a specific time period.  The text wont be formatted pretty when you copy it, but reformatting is better than rewriting!

Without further ado, here is the magic:

1
2
3
4
5
6
SELECT
a.last_execution_time AS ExectuionTime,
b.text AS Query
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
ORDER BY a.last_execution_time DESC

Comments

Posted by Jason Brimhall on 3 November 2011

Good info

Posted by sineethaparveen on 4 November 2011

Thanks for sharing :)

Posted by PRADEEP K. on 8 November 2011

Takes more than 3 mins to execute. Don't exactly know how long  because I cancelled it after 3 mins.

Posted by Henk Schreij on 9 November 2011

Runs fine for me in a few seconds. Too bad I did't know this query previously. It had saved me a lot of time.

Posted by chris.mcgowan on 9 November 2011

You could also use the SSMS Tools Pack by Mladen Prajdić.

http://www.ssmstoolspack.com/

Posted by Jon Miller on 9 November 2011

Really useful!

Worth sticking a "top 100" or something on it to limit the results, and probably correcting the ExectuionTime (sic) column name :-)

Lots of thanks though - I'm sure this will save me one day.

Posted by abha.agrawal on 9 November 2011

Thanks for sharing :)

Really very good info.

Posted by chrishurford on 9 November 2011

Run to text and there you have your formatting

Posted by vkirkpat on 9 November 2011

Very nice. Thank you for sharing... saving to my "utilities" query folder :)

Posted by Ryan Pavely on 9 November 2011

Odd that it shows the 'create procedure' and 'create trigger' syntax for executions.  Too bad you can't link it to dm_exec_requests to get the historic user_id.

Posted by sonofsith on 9 November 2011

Neat, thanks for sharing

Posted by trupti.darawan on 15 November 2011

Thank you very much for sharing. It is indeed very helpful :)

Leave a Comment

Please register or log in to leave a comment.