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:
No comments:
Post a Comment