Thursday, December 20, 2012

SQL 2012: msiexec.exe running constantly after SP1 install

Heads up on a SQL 2012 SP1 bug; this happened to me on two pretty bare 2012 installs:

Symptoms: 


After installing SQL 2012 SP1, 2 msiexec processes spawn and run constantly. These generate significant CPU and disk activity.

Description: 


An error with the installer causes the .NET NGEN (Native Image Generator) to run in an indefinite loop. According to one post, it may even cause registry bloat to the point where the registry reaches the maximum size and you may need to re-install the OS on the machine. :-/ That said, I haven't verified that claim.

Fix:

None yet available

Update: Microsoft posted the official fix (the same as the early one below) here.

Workarounds:


  1. (Per Steve Philip) "Uninstalling the Management Tools (both Basic and Complete) feature seemed to resolve the problem for us on most of our servers. On those that had the Data Tools feature installed, we also had to remove that."
  2. (Per the Microsoft SQL Team.. supposedly) "Hello all, we have identified a temporary workaround for this issue. We will continue to work on a permanent fix and will communicate more information as it becomes available.If you are currently experiencing this issue, perform the following steps appropriate for you CPU architecture: 
    1. - Open an elevated cmd.exe 
    2. - If you are on a 64-bit architecture machine, run both of the following commands in the cmd.exe window:
    3.      - %windir%\Microsoft.NET\Framework\v4.0.30319\ngen.exe queue pause
    4.      - %windir%\Microsoft.NET\Framework64\v4.0.30319\ngen.exe queue pause
    5. - If you are on a 32-bit architecture machine, run only the following command in the cmd.exe window:
    6.      - %windir%\Microsoft.NET\Framework\v4.0.30319\ngen.exe queue pause
    7. After running these command(s) CPU consumption should return to normal and the 1004/1001 events should cease being published to the event log.
    8. More information on ngen.exe can be found here: http://msdn.microsoft.com/en-us/library/vstudio/6t9t5wcf(v=vs.100).aspx
    9. Note - after a machine reboot, the workaround will need to be re-applied"
  3. (Per "DizzyBadger") "Just stop and disable ALL Microsoft .Net Framework NGEN services, and make sure you kill any lingering msiexec.exe processes. If your software hive has already reached 2GB on the other hand, you are screwed. (Again, unverified) Then you have to reinstall the OS, at least if it is a production machine. You can murk around in the registry and delete the keys manually, but there is no knowing what else has been scrambeled due to the fact that nothing can be added to the registry hive past 2GB. The bloated keys are HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v2.0.50727\NGENService &HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v2.0.50727\NGENService"
  4. (Update1; Per Paul D., see comments) Paul came up with a workaround to allow for the optimization tasks to still run; since we're still waiting on the official hotfix from MSFT you may want to have a look at that fix here.
There is another workaround listed on the site involving removing and re-installing the SQL management studio after patching, but this didn't work for me. 


Given the potential need to rebuild the OS it would make sense to check your production systems for this problem now.

Update 2: In the Microsoft connect article, MSFT has posted a fix, but gave the following warning about using it: "Please Note: The patch should only be applied if you're on SQL 2102 SP1. If you have already applied a Cumulative update or an "On Demand" fix on top of SP1, please wait for the associated hotfix update to be made available.


Thanks and regards
Shamik Ghosh
[SQL Server Team]
"

I can't speak to the success of this patch; I'll be waiting for the associated hotfix.

2 comments:

Paul Drangeid said...

I added a script that runs during off-peak hours so optimizations stil get run:C:\windows\Microsoft.NET\Framework\v4.0.30319\ngen.exe executequeueditems

followed up (about an hour later) with the ngen.exe queue pause solution posted above. I guess this is a compromise workaround until a solution is developed.

Toby Meyer said...

Thanks Paul! Good idea, I'll add it to the main post. Hopefully we get a real fix for this from MSFT soon.