CLR Errors – Follow Up

A few weeks ago I posted about an issue I was having with CLR failing on one of my production servers. While a reboot fixed the problem it was a temporary fix and happened on a couple of further occasions. Still not being able to find the root cause Microsoft recommended a complete removal and reinstall of the .NET framework.

One of the Microsoft support team advised running the following query which loads CLR and then traverses through all the registered assemblies. This quickly shows up any problems with CLR and SQL Server integration even if the problem is not showing elsewhere.

1. select * from sys.assemblies

Seeing the following is not a good thing:

Msg 6512, Level 16, State 27, Line 1

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005. You need to restart SQL Server to use CLR integration features.

Removing the .NET Frameworks

Firstly I downloaded the .NET Framework Cleanup Tool from http://blogs.msdn.com/b/astebner/archive/2008/08/28/8904493.aspx

With the tool installed on the server and all SQL services shut down and set to manual start I performed an uninstall of all of the .NET Frameworks in the order

• 3.5
• 3.0
• 2.0

The removal complete a server restart was in order. Once restarted it was time to

Reinstall the .NET Frameworks

Here’s the interesting thing, there’s actually no need to go through and reinstall each individual Framework, in fact just download and install .NET Framework 3.5 SP1 from Microsoft.

Once the install was complete and the server restarted I brought the SQL services back online (and reset their services to start automatically)

Checking sys.assemblies again gave me positive results

name      principal_id           assembly_id          clr_name               permission_set     permission_set_desc          is_visible                create_date                modify_date         is_user_defined

Microsoft.SqlServer.Types 4              1              microsoft.sqlserver.types, version=10.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil          3              UNSAFE_ACCESS  1              2008-07-09 16:20:00.070                2008-07-09 16:20:00.413 0

Since the reinstall I’ve not experienced further issues. It’s a shame that Microsoft were unable to give me a root cause to the problem, however it’s great to have it resolved.

Comments

[Hugo Camacho]

Hello, Nicholas... I am currently having that problem in my job's production environment. I have been searching the web for a solution to the problem, and your blog is the only source i have found so far. I have been thinking about performing the steps you took to solve the problem, but i am also concerned about removing the installed .net frameworks on the server as I don't know if there would be a change of something going wrong on the server at the end. We are running SQL Server 2008 (10.0.1600) ... if I remove the frameworks on the server, would .NET Framework 3.5 SP1 still be what I need to install back?. Help is greatly appreciated.

[Nic]

Yes, you should only need to reinstall the 3.5 SP1 framework back. I highly recommend testing this on a dev machine first.

Ultimately this is not what fixed the issue I was having. We found that we had an unpatched bug in Windows 2003 that lead to no TLS slots being available.
Take a look at http://support.microsoft.com/kb/941105

[Patrick Purviance]

So Nic, you are saying that the root cause was not a corrupt .NET Framework install, but was due to the Windows 2003 bug? Can you elaborate on that a bit? We've been living with the CLR errors in our Transactional Replication seemingly ever since we applied SQL Server 2008 SP2. Prior to that we'd not seen this error. Could be something else is the root cause, but we really need to find a permanent solution for our production environment. If the .NET 3.5 SP1 reinstall is not a fix, then we'd be willing to try the Windows hotfix seeing as how this is a 2003 x64 box. I likely will be reaching out to Microsoft myself if I can't get that warm fuzzy. I appreciate your post, as it has been quite helpful. Thanks, -Patrick