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.
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
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.