By: Jugal Shah | Comments (8) | Related: > SQL Server Management Studio Configuration
Problem
By default the Auto Recovery feature is enabled for SSMS and because of this when opening SSMS it may hang or become unresponsive for some time if the previous session was closed unexpectedly. There is not a way to turn this feature off from within SSMS, but we will look at how this can be done by modifying some registry entries.
Solution
Before turning off this feature we should discuss the benefits. By using the Auto Recovery feature in SQL Server Management Studio it will automatically save any open SQL scripts after a period of time. This feature is useful if SSMS crashed or the application was not shut down cleanly or the machine was shut down unexpectedly. If this was the case and you opened up SSMS you would see a screen similar to the below image.
Steps To Turn Off Auto Recovery
Step 1 - Open Registry
Go to Start -> Run -> RegEdit as shown below.
Step 2 - Find and modify registry value
In the registry editor go to the below path for SSMS 2005 and SSMS 2008 respectively.
- SSMS 2005 HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AutoRecover
- SSMS 2008 HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\General\AutoRecover
Double click on "AutoRecover Enabled", the value data will be "1" (ON), change this value to "0" (OFF) then press OK to save. This will turn off the auto recovery feature.
In you want to turn this option back on, just edit this value and set it back to "1".
To test this you can terminate the SSMS.exe/SQLWB.exe process from Task Manager and then open SSMS again to see if this works as planned.
Next Steps
- Read these other SSMS tips:
About the author
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
