CLR Errors In Replication

Did you know that .NET components are used in replication? I sure didn't until I tried to add a new publication and perform a snapshot.

I had not troubles creating the publication or adding the subscribers but when I attempted to run the snapshot I got the following error:

Source: Replication
Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
Exception Message: Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x800

Not a whole lot to go on there. There happens to be a DMV which will provide you with the status of CLR in SQL.

select * from sys.dm_clr_properties

I was expecting to see

name    value
-----------------------
directory    C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\
version    v2.0.50727
state    CLR is initialized

but instead I saw

name    value
-----------------------
directory  
version   
state    CLR initialization permanently failed

Time to open a support case with Microsoft.

After running through some troubleshooting steps with the MS engineers, including verifying the .NET install was good (see http://blogs.msdn.com/b/astebner/archive/2008/10/13/8999004.aspx) the decision was ultimately made to restart SQL to see if this resolved the problem.

After the restart everything was running smoothly and there were no issues performing snapshots. Sadly the restart eliminated any chance of getting a root cause for the problem. I've got a filtered dump setup to try and capture something in the event that it happens again. I kind of hope that it does just so we can find out exactly what the cause was.

Here are a couple of interesting notes that I found from this:

• .NET Framework 3.5 is just a wrapper for .NET 2.0
• You can log all .NET events to disk (see http://blogs.msdn.com/b/junfeng/archive/2004/02/14/72912.aspx)
• In the event SQL encounters a .NET/CLR error then it will fail and set a flag internally. That flag will not be reset unless the SQL Instance is restarted and so any CLR functionality will fail
• You want to have your SQL Instance configured to write dumps to a drive with more free disk space than you have memory on your server. By default dumps will be written to the log directory. You can change this by opening "Error and Usage Settings" from the start menu, going to options and changing the error reporting location. Note, you have to restart SQL for this to take effect as this information is read in at startup

Update on Wednesday, September 29, 2010 at 9:14PM by Nic

I've posted a follow-up to this post with steps for the permanent resolution to the issue at http://englishtosql.squarespace.com/english-to-sql-blog/2010/9/29/clr-errors-follow-up.html

Comments

[Chad Miller]

I too have been experiencing errors related to CLR during montly Microsoft security patches affecting the .NET framework. A CLR procedure would fail post-patch until SQL Server was rebooted (and yes the server was rebooted as part of patching). Although I've opened MS calls-no cause has been found. During one of the calls it was suggested to look at any customized permissions using caspool utility, but this too was a dead end. At this point rebooting has been the fix.

It's odd the CLR issues only occur during security patches which apply to the .NET framework and unfortunate CLR within SQL Server isn't more reliable. As a a workaround, we're removing code which calls the CLR procedure from our critical system.

[Nic]

Thanks for sharing that Chad.

What I found most shocking about this was that despite CLR not being enabled and us not using any kind of CLR procs that we were still at the mercy of .NET when it came to replication.

I'm hoping that having a dump trigger set on the snapshot error will yield some information if this happens again. Between that and the fusion logs we must be able to capture something.

[Marie J. Hernandez]

Great post. Thanks for sharing your experience. Religion would tell us that we should enjoy the cage and then if we do God would move you to a bigger and better cage :D If we are impatient with the cage then we are unenlightened, relbellious jerks...gucci watch