How to Turn Off SQL Server Management Studio SSMS Auto Recovery Feature

By: Jugal Shah | Read Comments (4) | Print Jugal has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2. Related Tips: More

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 Step

• 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

Related Tips: More | Become a paid author

Last Update: 4/15/2011

Share:

Share

Tweet

Free SQL Server Learning Waiting on Waits Let's connect on facebook! Tweet...tweet...tweet... Follow us on twitter

Comments and Feedback:

Friday, April 15, 2011 - 1:15:42 AM - Ankur	Read The Tip
It will be very stupid of someone to Turn Off Auto Recovery Feature in SSMS. Its a great feature to leave it enabled as it can be of great help when system gets hung while one is performing critical work.

Friday, April 15, 2011 - 10:59:49 AM - Mbourgon

Read The Tip

I'd be happy if the GD thing worked once in a while.  I've had it crash plenty of times, at least 50 over the years, and it's only once recovered ANYTHING.  I know there was (is?) a bug where it would only recover once per login - so if you left your computer up overnight, the next day a crash wouldn't get recovered.  So, I use SSMS tools, which will recover what you were running (although not what you'd been working on and not run).  Better than nothing, and definitely better than SSMS' Auto Recovery.

Monday, March 12, 2012 - 4:29:34 AM - tom	Read The Tip
does switching it of produce any benefits.

Tuesday, April 10, 2012 - 11:21:31 PM - mbourgon	Read The Tip
Tom: yes!  You don't get lulled into a false sense of security that it will ever work and save your butt.  Use SSMS Tools Pack instead.  That one DOES work. (no affiliation, just an ecstatic user)

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL doctor is the best SQL product on the market, by far. All of Idera's tools are great, but this is the icing on the cake!" Quickly and accurately deploy database changes with Red Gate's SQL Compare - the industry standard comparison and deployment tool. Need SQL Server help and not sure where to turn? Reach out to expert consultants for a Health Check. Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy. Join the over million SQL Server Professionals who get their issues resolved daily. Valuable SQL Server web casts on Performance Tuning, Development, Administration, SSIS and more...