Turn Off SQL Server Management Studio Auto Recovery Feature

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:
  • Change Default Value for Select Top n and Edit Top n Rows in SSMS
  • Steps to clear the server list in SQL Server Management Studio
  • Enabling IntelliSense and Refreshing IntelliSense Data in SSMS 2008
  • Run SQL Command Against Multiple SQL Server Instances Without CMS

Jugal Shah

Biography

Jugal Shah is the founder of SQLDBPool.com, where he is writes articles on SQL Server Administration and Development. Jugal has 8+ years of extensive experience in SQL Server Database administration and development. Jugal has worked on SQL Server 2000, 2005, 2008 and 2008 R2. In his current assignment, Jugal is managing large critical databases. His expertise is in database security, performance tuning and implementing high availability solutions. He can be contacted at jugal.shah@sqldbpool.com. He has been recognized as a SQL Server MVP since 2010.

• MSSQLTips Awards: Trendsetter (25+ tips) – 2013