How do I fix SQL Server assembly optimization errors and memory exhaustion?

Question

The .NET Runtime Optimization Service (mscorsvw) keeps consuming all my workstation's memory since I installed SQL Server 2012 Service Pack 1.

It consumes about 1.6 GB of memory before exhausting all available memory:

I can kill the process, but it happens again when I restart my computer or wait long enough.

Techdows suggests using the .NET Native Image Generator (ngen) to solve the problem.

I oipened a Command Prompt and ran commands like this:

cd C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319.1
ngen executeQueuedItems

The memory consumption of mscorsvw dropped to about 20-30 MB, and ngen consumed all the remaining memory.

ngen generated only error messages until I killed the process. The first few lines of output looked like this:

Microsoft (R) CLR Native Image Generator - Version 4.0.30319.1
Copyright (c) Microsoft Corporation.  All rights reserved.
Failed to load dependency Microsoft.DataWarehouse of assembly
Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The system
cannot find the file specified. (Exception from HRESULT: 0x80070002)
Failed to load dependency Microsoft.VisualStudio.DataTools.Interop of assembly 
Microsoft.DataWarehouse, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The system
cannot find the file specified. (Exception from HRESULT: 0x80070002)
Failed to load dependency Microsoft.Data.ConnectionUI of assembly
Microsoft.DataWarehouse, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The located
assembly's manifest definition does not match the assembly reference.
(Exception from HRESULT: 0x80131040)

All the messsages say either that the system could not find an assembly, or that a manifest definition does not match a reference.

As a workaround, I have disabled the Windows Services clr_optimization_v4.0.30319_32 and clr_optimization_v4.0.30319_64.

This hides the problem rather than solving it, and stops all .NET assemblies from being optimized.

What else can I try to solve this problem?

Answer 1

This problem is described on Connect (see msiexec.exe processes keep running after installation of SQL Server 2012 SP1) and a hotfix is available at KB2793634. There are work-arounds listed on the Connect post and this blog if you are unable to install the hotfix immediately.

I chose to setup 2 scripts. 1st script runs daily at 1am, and set to terminate after 1 hour of execution

c:\windows\Microsoft.NET\Framework\v4.0.30319\ngen.exe executequeueditems
c:\windows\Microsoft.NET\Framework\v2.0.50727\ngen.exe executequeueditems
c:\windows\Microsoft.NET\Framework64\v2.0.50727\ngen.exe executequeueditems 
c:\windows\Microsoft.NET\Framework64\v4.0.30319\ngen.exe executequeueditems

2nd script runs daily at 3am (and whenever the computer restarts):

c:\windows\Microsoft.NET\Framework\v4.0.30319\ngen.exe queue pause
c:\windows\Microsoft.NET\Framework64\v4.0.30319\ngen.exe queue pause

This way I still get compile optimization jobs (only run during off-peak hours), but the mscorsvw/msiexec process is tamed.