How to clear the SSMS cache?

If you are reading this, it's probably because you too have this rare need to clear the SSMS cache for some kind of troubleshooting. 

Generally, when I am troubleshooting a connection issue that I wonder is due to a cached or stale connection, the first things that come to mind are clearing the DNS client cache and, occasionally, the ARP cache. Rarely, if ever, do I consider the SSMS cache.  Of course, there are other reasons why you may want to clear the cache.

Since almost everything in computer world uses some kind of cache, no surprise that SQL Server Management Studio (SSMS) too has it's own cache that it uses to make our lives a bit easier with features like auto-completion, syntax highlighting, remembering object names, our preferences and even idiosyncrasies.  The cache is stored in what Microsoft refers to as a cache file located in your user profile directory. The cache files are not human-readable per se, nor do I currently have any desire to read them.  And nor am I right now concerned with the size of cache and/or frequency with which it refreshes it.

And depending on the version of SQL Server Management Studio (SSMS), the cache folder also contains one or more settings and configuration files. For example, in the case of SSMS 20.x, one such file is NewSettings.vssettings (first introduced in SSMS 17.x), which stores your customized preferences and environment settings,  basically all the options and settings you see here:

The file is created or saved (if it already exists) whenever you close SQL Server Management Studio (SSMS).

If you have customized your SSMS appearance and experience significantly, you should consider backing up this file. It is located at "%USERPROFILE%\AppData\Local\Microsoft\SQL Server Management Studio\%Version%\Settings\SQL Server Management Studio".

A couple of other configuration files you may want to save are Ssms.exe.config and RegSrvr*.xml.

Clearing the cache per se is simple. First, if you have SQL Server Management Studio (SSMS) running, make sure to close it. Copy the following folders somewhere safe in case you need them, then delete them entirely. Alternatively, you could also move (cut and paste) these folders to another location.

• "%USERPROFILE%\AppData\Local\Microsoft\SQL Server Management Studio"
• %USERPROFILE%\AppData\Roaming\Microsoft\SQL Server Management Studio"

There is a Microsoft article on clearing everything from SSMS cache:

https://learn.microsoft.com/en-us/sql/ssms/troubleshoot/ssms-troubleshoot?view=sql-server-ver16#clear-ssms-cache-files

When you relaunch the SSMS after clearing the cache, you will see the following warning, which you could ignore if you want to start SSMS with a blank slate, or as I just did a bit ago, save the settings files somewhere before deleting the cache files, then copy it back to its original location.

Here is a PowerShell script to backup then clear the SSMS cache:

# Current User's profile location
$ProfilePath = $env:USERPROFILE

# Backup location
$DateTimeSuffix = Get-Date -Format 'yyyyMMdd_HHmmsss'
$BackupPath = Join-Path $ProfilePath "Documents\SQL Server Management Studio - Backup\$DateTimeSuffix"

# Create the backup directory if doesn't already exists
New-Item -Path $BackupPath -ItemType Directory -Force

# Get the folder names where SSMS cache files are stored
$SSMSFolders = Get-Item "$ProfilePath\AppData\*\Microsoft\SQL Server Management Studio"

# Move the SMSS cache folders to Backup directory
ForEach($SSMSFolder in $SSMSFolders)
{
    $DestPath = Join-Path $BackupPath $SSMSFolder.Parent.FullName.Replace("$ProfilePath\AppData\", "")
    New-Item -Path $DestPath -ItemType Directory -Force
    Move-Item -Path $SSMSFolder.FullName -Destination $DestPath -Force
}