Fix: The Instance ID 'MSSQLServer' is Already in Use by SQL Server Instance 'MSSQLServer.Inactive'

Problem:

You attempted an in-place upgrade of an existing default instance of SQL Server. The upgrade failed for one reason or another. When you attempt to re-run the Installation, it can no longer detect any instance to upgrade. Attempting to install a new default instance results in the error below:

THE INSTANCE ID ‘MSSQLSERVER’ IS ALREADY IN USE BY SQL SERVER INSTANCE ‘MSSQLSERVER.INACTIVE'

Trying to run uninstall/repair using the old version also no longer detects any instance. This leaves you unable to create a new Default instance on the server.

Cause:

Due to the failed upgrade, the instance was left orphaned. The instance ID MSSQLSERVER is in use and still appears in the Registry.

Solution:

Correcting this issue properly would be to cleanly uninstall the orphaned instance with the instance ID “MSSQLSERVER.INACTIVE.”  The catch is you won’t see this listed when running the setup through the GUI.

You may verify its existence by re-launching the setup for the new version you are trying to use. 

From the SQL Server Installation Center, go to Tools > Installed SQL Server Features Discovery Support.

This will launch the HTML report for instances and features installed on the server.

Here, you can clearly see the orphaned instance. 

To remove this cleanly, launch a command prompt and navigate to where the Installer for that version is located. 

From here, you can launch the uninstall command:

setup /q /action=uninstall /instanceid=MSSQLSERVER 

You can either use the command above to uninstall all features under the instance ID, or you can also specify the features listed. For example, for the one listed in the report above:

setup /q /action=uninstall /instanceid=MSSQLSERVER /features=DQ,FULLTEXT,SQL_INST_MPY,SQL_INST_MR,ADVANCEDANALYTICS,SQLENGINE,REPLICATION,AS

After this command runs successfully, go back to the Installation Center and relaunch the SQL Server Discovery report. This should now show up cleanly, as below.

After that, if you have not uninstalled the Old Instance as part of your troubleshooting, address the failure/error that caused the failed upgrade. Usually, it is the additional features that cause this, i.e., SSAS, ML, R Language, etc. If it is a feature that you are not using, uninstall the feature before attempting an upgrade and/or run the repair for the old version. In any case, this should let you either upgrade or reinstall a Default Instance.