Sunday, March 6, 2016

Recover lost SQL Server SSMS scripts

This is a good one.  I'm working a problem earlier today in SSMS, and I didn't close a WHILE LOOP properly.  Rather than telling me this, SQL just runs, and runs, and runs... I couldn't kill it. I couldn't cut/paste/copy my code. I couldn't do anything. So I take a screenshot and kill it in Task Mgr. But - it's not just my bad code.  I lose my entire SSMS session.  Crap.  

I remember seeing something like this before, and I check this directory:



Nadda.  Then I check this one:



Again, nothing. I put a lot of time into this; even w/the bad WHILE LOOP, I still wanted my code! Then it hit me --- the cache! I've worked with sys.dm_exec_query_stats recently, which contains the cached plans. Wouldn't it be in there?  Yep!!  It was, and I was able to scrape my code back together. SSMS crashes, or you close it too quickly without saving your code.  You can get it back!

     USE DBA; --- change this to your database
     SELECT
        execquery.last_execution_time [Date],
        execsql.text [Script]
     FROM
        sys.dm_exec_query_stats AS execquery
           CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) execsql
     ORDER BY
        execquery.last_execution_time DESC  

Your results will return any statements that have been cached, like this:

Not bad at all.  This DMV is very useful for query plan analysis and performance troubleshooting.  Definitely worth a look.

No comments:

Post a Comment