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

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).

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.

Comments

Posted by Jason Brimhall on 25 May 2011

nice tip.  Very useful indeed.

Posted by Jon Russell on 27 May 2011

Grant,

You are not the only dummy. I do it often. Thanks for this tip. Now I can continue my stupid ways more efficiently. ;)

Jon

Leave a Comment

Please register or log in to leave a comment.