Tag: Errors

SQL

Enabling CLR On Your SQL Server Instance

Strange one today. A dev came over with a strange question. He was trying to enable CLR on his local SQL instance but getting an error when trying to do so. Curious I thought, so I took a walk over to his desk to see what was going on.

He was executing a basic sp_configure script to enable CLR, nothing special, no frills.

EXEC sp_configure 'clr enabled', 1

RECONFIGURE

When he ran this he was getting an error:

Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.

Most curious. I went back to my machine and wasn’t able to reproduce the problem. Rather than have him attempt to restart his instance I just had him reconfigure with override

EXEC sp_configure 'clr enabled', 1

RECONFIGURE WITH OVERRIDE

This time it ran correctly and gave a good result.

All the “WITH OVERRIDE” does is force SQL to be reconfigured with the updated value (unless it would cause a fatal error). Something to remember if this ever crops up for you.

SQL, Windows

MSDTC Failing In A Cluster

I’m merrily working away on installing SQL 2008R2 on a bunch of new clusters. The SQL installs have gone fine and I’m getting set to install MSDTC, one for each SQL instance (read the awesome post by Cindy Gross on this).

The install of MSDTC went smoothly and it seemed very happy. Then I failed over the cluster…

MSDTC failed.

It wouldn’t restart.

I failed back to the original node and it wouldn’t start there either.

 

What’s the error?

I dumped the last few minutes of the cluster log by opening a dos box command prompt and running…

cluster log /gen /span:5

 

This dumps the last five minutes of cluster logs into C:WindowsClustercluster.log

I scrolled through to get to the MSDTC error piece:

INFO  [RES] Physical Disk: Failed to get vol MP root for path ?, status 123
ERR   [RHS] Error 123 from ResourceControl for resource <instance>_MSDTC.
WARN  [RCM] ResourceControl(STORAGE_IS_PATH_VALID) to <instance>_MSDTC returned 123.

I checked the disk resource for MSDTC and it was online. Looking at the filesystem on that disk and there was an MSDTC directory, so I knew there were no access problems. It didn’t make any sense.

 

So what’s going on?

The key error here is the failure to get MP root for path ?

Apparently MSDTC is not supported does not work with Mount Points, which is what I had set the dependency to. There were no warnings on this when setting MSDTC up and I’d not seen or heard of any documentation that speaks to this.

I was finally pointed to a Connect item opened by someone who’d had the same issue https://connect.microsoft.com/SQLServer/feedback/details/576545/msdtc-fails-to-restart-in-sql-server-2008-r2-clustered-group

Side note: I love it when Connect items such as this are closed as by design. Why is this by design? Can someone explain to me why MSDTC shouldn’t be supported on Mount Points?

 

I deleted the MSDTC resource and added it again, this time using a regular drive as a dependency and everything worked perfectly. I was able to failover and have SQL perform distributed transactions.