SQL Server – Upgrade fails – “The Instance ID ‘MSSQLSERVER’ is already in use by SQL Server instance ‘MSSQLSERVER.INACTIVE’.”

This is one of the very frequent errors we receive when we try to upgrade to 2008 or 2008 R2 or to that matter even to 2005. The upgrade may fail for any reason initially and later when you fix that issue and retry to upgrade it keeps on crashing by showing the below error message.

The Instance ID ‘MSSQLSERVER’ is already in use by SQL Server instance ‘MSSQLSERVER.INACTIVE’.

To continue, specify a unique Instance ID.

In the registry you will see the below key.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\Inactive

Most of the time you try to rename this key and the some other keys in registry and try the install. It may sometimes help but fail sometimes. The reason being the components of 2008/2008R2 to which you are upgrading to may not have completely installed correctly. They may be in an inconsistent state which will not allow your upgrade to complete.

So the best solution here is to completely uninstall the database engine components of the 2008/2008R2 and start the fresh upgrade. Here is the actual challenge. If you try to uninstall this from add and remove programs it does not show the inactive instance to be uninstalled. So how to proceed?

However userfriendly the GUI is cmd prompt comes handy in these kind of situations. Run the below command to uninstall the inactive instance by using the setupDOTexe of 2008/2008R2 or which ever you are trying to upgrade to (Make sure you are using the right setup file and the right instance name. You may end up uninstalling a working instance if you choose the wrong parameters).

setup /action=uninstall /instanceid=MSSQLSERVER /features=SQLEngine,Replication,FullText

After the uninstall completes successfully you can go ahead with the upgrade again to complete successfully.

Happy Troubleshooting,

Regards

Manohar Punna

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook