Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Bradley Schacht

Bradley is a consultant at Pragmatic Works in Jacksonville, FL. He was an author on the book SharePoint 2010 Business Intelligence 24-Hour Trainer and tech edited the SQL 2011 Bible. His experience on the Microsoft BI platform includes DTS, SSIS, SSRS, SSAS and migrations and conversions. He has helped numerous companies in successfully developing and implementing new business intelligence solutions into their organizations. Bradley also participates as a speaker in community events like SQL Saturday, Code Camp, SQL Lunch and SQL Server User Groups. He is a contributor on sites such as BIDN.com and SQL Server Central as well as 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.