How to Fix a failed SQL 2005, 2008, R2 or 2012 Install/Upgrade - MSSQLSERVER.INACTIVE

It doesn't matter what version of SQL you're on, upgrading to etc...this issue can pretty much be resolved using this process (including pre-release/CTP/Beta/old-to-new etc.), although you might always be the exception :-) This took me a long time to figure out, so I hope it helps you out!

Problem/Sympton: I went to upgrade a perfectly working SQL 2008 R2 standard server (which was using a Default Instance, nothing crazy) to SQL 2012. The Setup failed halfway through with a random error connecting to SQL Analysis Services (your failure could be different)...then it indicated all I had to do was fix the problem and re-run my SQL 2012 Upgrade...this wasn't true :-)

Error/Result: I had an "orphaned SQL instance" called MSSQLServer.Inactive and no matter what I tried, I could not get this to "uninstall"

The most-recommended Resolution to the Error/Result: Your internet search will most likely lead you to attempt many things, none of which will work, and finally to the suggestion of trying to uninstall the Inactive SQL Instance using the command line

setup.exe /ACTION=uninstall /FEATURES=Feature_List /INSTANCENAME=Instance_Name

The REAL Resolution Steps: (don't bother trying to figure out what went wrong...feel free to try the most-recommended resolution first if you'd like...but I'd suggest that if you have the above error/result, do the following:

  • Search your machine for files called Datastore_Discovery.xml (depending on how much you've got or done, you might see many of these...just open the most recent one and you'll be fine
  • Open Datastore_Discovery.xml in an editor...given the formatting, what worked for me was in Visual Studio 2008/2010 and clicking Edit, Advanced, Format Document (this arranges the XML nicely)
  • What you're looking for is all places where MSSQLServer.Inactive exists in the document as the Instance ID...below is a sample of how the line starts:
    • <Instance Urn="Machine[@ID='IWE']/Product[@ID='SQLVNEXT']/Instance[@ID='MSSQLSERVER.INACTIVE']" ID="MSSQLSERVER.INACTIVE" Name="MSSQLSERVER.INACTIVE" ........
  • Scroll along the lines around the above one and find the following value field: ProductCode="{9FFAE13C-6160-4DD0-A67A-DAC5994F81BD}" (and of course, don't ACTUALLY use the code you see here...yours will be different...look for the brackets and use yours :-) )
  • There might be multiple ProductCodes, depending on how many times the <Instance URN...> line lists MSSQLServer.Inactive, so make sure to find all ProductCodes...BUT BE CAREFUL that you ignore any VALID instances of SQL. Only look for the ones under MSSQLServer.Inactive entries.
  • Now open a command line
  • For each of the ProductCodes that you found for the Inactive SQL Instances, type the following:
    • msiexec /x {9FFAE13C-6160-4DD0-A67A-DAC5994F81BD}
  • Do this for each Product Code that relates to an Inactive Instance
  • Once complete, go back to your SQL installation media and run Setup
  • Once the main splash screen launches, click on Tools, then Installed SQL Server features discovery report
  • This will launch a web page listing the SQL Instances and all features per instance
  • If you still see any MSSQLSERVER.INACTIVE there, you missed one of the ProductCodes and you need to double check. Otherwise, all of the orphaned SQL Instances should now be gone, and you can either Add Features or do a clean Default Instance reinstall

Note: to make sure the reinstall/install works smoothly, make sure you do the following:

  • Go into Add/Remove Programs, click on the SQL entry that shows (Setup) next to it...forget the underlying pieces of SQL...just the "main SQL program" and Uninstall
  • When that finishes, open a command prompt and do the following:
    • Navigate to c:\windows\system32
    • Do a directory listing of files using the command dir *perf-*.dll and you should get a list back...look for the SQL ones (mine included perf-MSSQLSERVER-sqlctr11.0.2100.60.dll, perf-MSSQL11.MSSQLSERVER-sqlagtctr.dll, perf-ReportServer-rsctr11.0.2100.60.dll etc.)
    • You now need to move just the ones specific to the SQL instances that relate to your SQL that won't install (if you want a clean wipe of all SQL, remove all that show SQL in the file names)...using the command move perf-MSSQLSERVER-sqlctr11.0.2100.60.dll c:\temp for example
    • Once you've removed them, you're good to go on the SQL side to re-run your Setup
    • If SQL Setup says there's a pending reboot, see the next note below...
  • Also, if you want to shortcut things and not bother with the Server Reboot that SQL is about to force you to do, you can do the following:
    • Start, Run, Regedit
    • Navigate down to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
    • Look for the "PendingFileRenameOperations" key and delete when you run SQL Setup (or hit Re-Run on the one you have open)'ll proceed without a hitch

I hope this helps!