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

By:   |   Updated: 2011-04-15   |   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.

turning off the auto recovery feature in ssms

Steps To Turn Off Auto Recovery

Step 1 - Open Registry

Go to Start -> Run -> RegEdit as shown below.

run the regedit as shown

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 the registry editor go to the path for sql server 2005 and sql server 2008

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jugal Shah 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


Article Last Updated: 2011-04-15

Comments For This Article




Monday, February 3, 2020 - 4:39:55 PM - Clint Herring Back To Top (84129)

In SSMS change on Tools>Options>Import and Export Settings

Friday, February 6, 2015 - 5:40:46 AM - nagesh Back To Top (36173)

Hi, Anyone know how to disable Auto Update in SQL Server Management Studio (SSMS) 2012.

 

Tuesday, September 2, 2014 - 4:09:13 AM - Rama Back To Top (34346)

In the registry editor,  HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\ after going here i did't find "General\AutoRecover"

there is no option to modify.


My issue is"Auto Recovery which kick in after every 5 mins on User system where it tries to save files on Server but User is getting error message Windows Delayed Write Failed "


Please assist.

Wednesday, January 8, 2014 - 12:18:03 AM - Mukesh prajapat Back To Top (27984)

Dear Sir/Madam

 

I want turn off auto recovery in sql server management studio.

 

For SSMS 2008, Go to the key HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\General\AutoRecover
- Change DWORD "AutoRecover Enabled" to 1 for Enabling and 0 for Disabling Auto Recovery

 

I using this steps for turn off auto recovery but not turn off auto recovery.

 

Please solve this problem.

 

Thanks

 

Mukesh

Tuesday, April 10, 2012 - 11:21:31 PM - mbourgon Back To Top (16841)

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)

Monday, March 12, 2012 - 4:29:34 AM - tom Back To Top (16342)

does switching it of produce any benefits.

Friday, April 15, 2011 - 10:59:49 AM - Mbourgon Back To Top (13619)

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.

Friday, April 15, 2011 - 1:15:42 AM - Ankur Back To Top (13617)

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.














get free sql tips
agree to terms