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 the computer world uses some kind of cache, it's no surprise that SQL Server Management Studio (SSMS) also has its own cache. This cache makes our lives easier by remembering things like most recently used (MRU) connections and objects, and enabling features like IntelliSense, auto-completion, syntax highlighting, object names, our preferences, and even idiosyncrasies.  To elaborate on how the cache aids the IntelliSense feature, it stores local copies of database schema metadata, such as tables, columns, and functions, which allows IntelliSense to quickly provide accurate auto-completion suggestions as you type. This enables context-aware recommendations, tracks object dependencies, and improves performance by avoiding repeated database queries. In large, active environments with many DBAs and developers, this can significantly enhance both performance and team productivity.

However, during active database design and development phases where schema changes occur frequently (such as adding new tables or columns), IntelliSense may not immediately recognize these changes until the cache is refreshed.

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 (it would be a security risk if anyone got hold of those cache files), nor do I currently have any desire to read them. And I'm not concerned right now with the size of the cache or the exact algorithm it uses to decide when to automatically refresh it. However, you can use the keyboard shortcut CTRL+SHIFT+R to manually refresh it. This ability to manually refresh the cache comes in handy if you are part of a team that is actively working and creating new database objects.

One way you can use SQL Profiler or Extended Events to see which queries are used to populate the cache. These queries will appear as being executed by 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense' under your login.

*SSMS does not currently update the IntelliSense cache aggressively, nor is it as instantaneous or automatic as one might expect. Therefore, after creating, altering, or dropping database objects, a manual refresh using the shortcut Ctrl+Shift+R is required. Otherwise, SSMS may display outdated schema information in the query window.

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:

Click on Tools menu on top then Options:

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".

There are some other configuration files you may want to save, such as Ssms.exe.config, UserSettings.xml, and RegSrvr*.xml. Please scroll down to the end of this article for a brief description

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:

Clear SSMS Cache Files

Then when you relaunch the SSMS after clearing the cache, you will see the following warning, which you could ignore by clicking OK 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 NewSettings.vssettings back to its original location.

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

# Check if SSMS is running
$ssmsProcess = Get-Process -Name "Ssms" -ErrorAction SilentlyContinue

if ($ssmsProcess) {
    Write-Warning "SSMS is currently running. Please close SSMS before running this script."
    Throw "SSMS is currently running. Please close SSMS before running this script."
}

# Current user profile location
$ProfilePath = $env:USERPROFILE
Write-Output "User Profile: $ProfilePath"

# Backup location with datetime suffix
$DateTimeSuffix = Get-Date -Format 'yyyyMMdd_HHmmsss'
$BackupPath = Join-Path $ProfilePath "Documents\SQL Server Management Studio - Backup\$DateTimeSuffix"
Write-Output "Backup Location: $BackupPath"

# Create the backup directory if it doesn't already exist
New-Item -Path $BackupPath -ItemType Directory -Force | Out-Null

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

if ($SSMSFolders -eq $null) {
    Write-Warning "Warning: No SSMS folders found"
} else {
    # Move the SSMS cache folders to Backup directory
    foreach ($SSMSFolder in $SSMSFolders) {
        $RelativePath = $SSMSFolder.Parent.FullName.Replace("$ProfilePath\AppData\", "")
        $DestPath = Join-Path $BackupPath $RelativePath

        Write-Output "Moving contents of folder $SSMSFolder to $DestPath"

        # Create destination directory if needed
        New-Item -Path $DestPath -ItemType Directory -Force | Out-Null

        # Move folder contents to backup
        Move-Item -Path $SSMSFolder.FullName -Destination $DestPath -Force
    }
}

Finally, here is a brief description of some of the configuration files used file by SSMS:

• NewSettings.vssettings: Stores user environment and IDE settings such as fonts, colors, window layouts, and editor preferences.

• Ssms.exe.config: This is the main configuration file for SSMS and  stores runtime settings, assembly bindings, and other .NET framework configuration directives needed for SSMS to run.

• UserSettings.xml*: Stores specific customization and preferences for SSMS UI behavior, including tool windows, recent files, and other user-level options.

• RegSrvr.xml*: Stores local registered SQL connection information, such as saved server names, authentication types, and connection properties for servers previously registered in SSMS.

*An important difference to keep in mind between UserSettings.xml and RegSrvr.xml: UserSettings.xml caches your recent ad-hoc connections automatically, while RegSrvr.xml holds your explicitly saved registered server groups. In other words, connections stored inside UserSettings.xml are maintained/cached automatically by SSMS, whereas RegSrvr.xml is for manually maintained registered server lists.