How to survive SSMS Crash

So I was just about to finish the greatest piece of tSql code ever conceived (/*with multiline comments*/) when SSMS crashed suddenly/box was restarted/rebooted/lost power/suffered rapid unscheduled disassembly etc. When I opened Management Studio for the first time after the crash there was only single script recovered that was anything but my Magnum Opus. Of course I can’t go back in time and retrieve it from my memory because that clearly was one off and now planets are not aligned. What should I do next?

SSMS Script AutoRecovery Options

Based on my experience SSMS is actually quite good at keeping “unsaved” work intact during these types of situations that, just like deadlocks, can happen anytime and should be handled properly. There might be things you are not aware of or don’t realise in the heat of situation. The most important part is to breath. Logical explanation - there were multiple SSMS instances running before crash and only one was recovered. Small variation on above is when SSMS instances used different security context. You should have a look at content of:


Also I remember helping colleague to recover precious 400 something lines sproc from Sql Server plan cache. If you executed your script accidently (which never happens) - it still might be there. Another place to look at is QueryStore.

Jan Rokicki avatar
About Jan Rokicki
Sql Server Expert, Azure Solutions Architect and Machine Learning practitioner.
comments powered by Disqus