A faster CHECKDB – Part IV (SQL CLR UDTs)

I have been working on the various aspects of DBCC performance and SQL CLR based User Defined Data Types.    I encountered a few issues that I have outlined below.  1.      Memory Grant Bug There is a bug, prior to SQL Server 2014, causing the memory grant for the DBCC operations (checktable or checkdb per table)…

0

A performance issue involving CLR garbage collection and SQL CPU affinity setting

We have been troubleshooting a customer’s case and uncovered a GC behavior with SQL Server CPU affinity that is worth sharing here in a blog. Problem Customer reported that they had two instances of SQL Server 2008 running on a two-node cluster.  Let’s call them Instance1 and Instance2.  When they run both instances on the…

0

Unable to register .NET framework assembly not in the supported list

SQL Server has supported CLR usage since version 2005.  But support of .NET framework assemblies within SQL Server is limited per our support policy in KB http://support.microsoft.com/kb/922672.   Some users chose to use .NET framework assemblies outside the list in KB http://support.microsoft.com/kb/922672.  This can cause various issues.   Lately we have had a few reports of…

6

AppDomain unloading messages flooding the SQL Server error log

This blog is built directly from a customer reported issue.  As I helped investigate the source of the issue I thought it would be of interest to a broader audience – hopefully you find this interesting, as well. Allow me to provide some history of the problem before I dive into extended details. The SQL…

1

MS11-028 may cause issues with SQL, Exchange and PowerShell

A .NET Security Update (MS11-028) may end up causing issues with applications that make use of the .NET Framework, mostly resulting in applications being unable to launch, or loss of functionality within Native Applications that make use of .NET. In some cases, it could lead to an application crash. For this issue to occur, the…

2

Deploying CLR Assembly hangs with Visual Studio 2010

following upgrading Visual Studio 2010 from 2008, the  customer started to experience problems when debugging CLR assemblies.  The behaviors are: Deploying (debugging will automatically invokes deploy) a CLR assembly will take a long time Deploying a CLR assembly may fail with error “Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification” after…

1

Unable to load CLR assembly intermittently

Recently, I worked with a customer on an CLR assembly loading issue. Intermittently, they would receive the following error. Msg 10314, Level 16, State 11, Line 1An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. The server may be running out of resources, or the assembly may not be…

1

How It Works: Are you handling cancels correctly in your SQLCLR code?

I was recently developing a set of SQLCLR functions and procedures for an internal project.    One of the tests I added to my suite was to cancel the query (attention) and make sure I handled it properly in my .NET implementation.    What I found was much more than I expected and it is something that…

2

Timeout when deploying SQL CLR objects from Visual Studio 2005 or 2008 but not from Management Studio

We have had a few customers who want to deploy their assemblies using Vistual Studio.   They encountered various errors similar to below.  But when the use SQL Server management studio to manually CREATE ASSEMBLY, everything works fine. Error: starting database upload transaction failed. Error: The operation could not be completed   Deploying file: TEstAssembly.dll, Path:…

0

Unable to run SQL CLR procedure with System.Security.SecurityException

Recently, we have troubleshoot a customer issue related to SQL CLR.  The problem is that the assembly is configured to use external_access but he kept getting an exception similar to this (this is a sample call stack): Msg 6522, Level 16, State 1, Procedure sp_test, Line 0A .NET Framework error occurred during execution of user-defined…

0