May 25 2011

Oh ****!

ohnoDid 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.

Tags: , , , , , ,

categories SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication | | 25 May 2011 |

14 Comments

  • By Jamie Thomson, May 25, 2011 @ 8:49 am

    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

  • By Grant Fritchey, May 25, 2011 @ 8:57 am

    Excellent!

    Thanks Jamie. I knew I’d read it recently.

  • By Shawn Melton, May 25, 2011 @ 10:02 am

    Love the picture…very good attention grabber. Matches the title very well :)

  • By Grant Fritchey, May 25, 2011 @ 10:08 am

    Thanks. I can’t claim credit. It’s from the Creative Commons on Flickr. URL is embedded.

  • By Peter, May 25, 2011 @ 12:29 pm

    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.

  • By Grant Fritchey, May 25, 2011 @ 12:36 pm

    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.

  • By Peter, May 25, 2011 @ 12:39 pm

    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).

  • By Todd Everett, May 26, 2011 @ 9:20 am

    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!

  • By Brad Schulz, May 26, 2011 @ 12:43 pm

    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

  • By Valentino Vranken, May 27, 2011 @ 2:11 am

    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!

  • By David, May 30, 2011 @ 10:21 pm

    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.

  • By Jay Bonk, June 22, 2011 @ 12:52 pm

    Grant,

    Awesome tip – thanks very much. I had the same Oh ****! moment this morning,and I remembered reading your post awhile back.

    Thanks again…..

  • By Grant Fritchey, June 22, 2011 @ 1:11 pm

    Glad it was helpful.

Other Links to this Post

  1. 2011 Yearly Link Roundup | Brent Ozar PLF | Brent Ozar PLF — December 26, 2011 @ 11:01 am

RSS feed for comments on this post. TrackBack URI

Leave a comment