Did you ever accidentally close SQL Server Management Studio? And, in closing SSMS, did you get the prompt that says “Save changes to the following items?†And did you, completely unthinkingly, with a query you had just been working on, hit Cancel? Yeah, me neither. What kind of idiot does that….
OK. I confess. I just did that. Silly thing it was, but I had just spent at least 1/2 an hour working on a query and now it was gone…. or was it? I had just run the query and had been looking at the results when I closed SSMS. Initially, I panicked and started thinking about how I could get the data back (somewhere there’s a file I’ve heard). Then it occurred to me, I had just been writing queries against the cache using DMOs. Why don’t I just pull it using the DMOs I had just been using? Worked like a charm.
SELECT dest.text
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 > '5/19/2011 11:00'
AND dest.text LIKE 'WITH%';
My query was much more complicated, but this was all I needed. I was able to filter out the junk in cache by only selecting stuff with an execution time within about 15 minutes of when I had closed SSMS, and I supplied the start of the query, a CTE. That was all I needed. I got back my query. Took a little formatting work, but it was immediately available and all was right with the world.
Just posting this in case you hit the same situation. You too can get your query back, easily and quickly. Then again, maybe I’m the only one stupid enough to do that.
Hi Grant,
“(somewhere there’s a file I’ve heard)”
indeed there is 🙂
Recover Backed-up query files in SQL Server Management Studio
(http://sqlblog.com/blogs/jamie_thomson/archive/2010/07/19/recover-backed-up-query-files-in-sql-server-management-studio.aspx)
Regards
Jamie
Excellent!
Thanks Jamie. I knew I’d read it recently.
Love the picture…very good attention grabber. Matches the title very well 🙂
Thanks. I can’t claim credit. It’s from the Creative Commons on Flickr. URL is embedded.
Gotta put in a plug for SSMS Tools – that save query feature is awesome and the color-coded windows actually work when you switch connections. There are a lot of other useful features as well, but those two have been very, very useful to me.
It is a great tool, but I don’t think it would have helped in this exact situation because I closed SSMS all the way and if I recall correctly, the recently called query list doesn’t maintain between sessions. Could be wrong on that one though.
It saves periodically to either file (default) or DB Table. Jamie’s solution is probably a better one, but I’ve found that SSMS tools is really helpful when I’ve run a query in the last couple of days and am asked to run it again. Saving intervals can be changed (and assumes that you’ve actually run the query).
Great post! I do this more often that I’d like to admit. Thanks for this tip on using the cache and also the tip from Jamie if it turns out the query got backed up by SSMS. I’ll be putting these into my files!
Great story!
BTW, you could have saved yourself the formatting work by using the XML processing-instruction() directive:
SELECT MyFormattedCode=
(select [processing-instruction(q)]=
N’:’+nchar(13)+dest.text+nchar(13)
for xml path(”),type)
–Brad
Nice one Grant, good thinking coming up with that query in such a situation! But I do think the button that you’re referring to is the No and not the Cancel button. The Cancel button is the one you wished you’d clicked!
And I can know, I’ve got heaps of experience with that button 😉
PS: great addition in Brad’s comment, learned something today!
Very cool, linked over to this from Brenda news letter. I think this had happened to he best of us at some point. It’s good to keep this around cause you do stuff like this at the least opportune moments.
Grant,
Awesome tip – thanks very much. I had the same Oh ****! moment this morning,and I remembered reading your post awhile back.
Thanks again…..
Glad it was helpful.
[…] Ever accidentally closed a tab in SSMS without saving the T-SQL first? Or even worse, closed SSMS entirely? Grant Fritchey shows you how to get it back. […]
[…] you ever want to find the history of queries on your SQL Server? Just follow this useful tip from our friends at The Scary […]
thansk!!!
Gr8 help… much appreciated
For sql execution history, you can try http://www.ssmstoolspack.com/ – it’s free for sql server 2005/2008.
Hi Grant,
Excellent explain! you really save my life because I met the same thing as you! lol
btw, if the history already execute months ago, is it possible to find it also?
Hey Terence,
Yeah, it’s possible that you could see stuff months later. It completely depends on the level of thrash in your cache. It might be there for months. It might be there for minutes. Also, server reboots will obviously clear it out.
Hi Grant,
Can you please explain what exactly this should retrieve? I have a case in which a table was dropped from a db and I’m trying to find the code that dropped it. I ran your query and got some old transactions but not the ”drop table” i’m looking for. Your query returns all the queries on the server or the queries that ran from your specific ssms client?
Thanks!
The query returns whatever is currently in the cache. It’s possible that the query has aged out of cache. If the query isn’t in cache and you don’t have other monitoring enabled (trace events, extended events, or Query Store in 2016 or above), there’s no way to retrieve that query.
Great!!
The key are:
get queries executed more heavies by day or week
– Find Most Expensive Queries and find more slow queries
– Heavy processes in sql server
Hunting down bad SQL query performance
If you’re really focused on information of that type, either Query Store or Extended Events will serve you better. The DMVs are so cache dependent they can be somewhat unreliable.
Still coming in helpful in 2020, Grant. Thanks 🙂
Thanks!