How to fix Error 3810 on Windows Server 2025 / Windows 11

by

Why This Error Happens

Error 3810 occurs when a CLR assembly is dropped or altered while an active event notification still depends on it, causing SQL Server to forcibly remove the notification to prevent an orphaned reference. This typically happens because the assembly was recompiled, redeployed, or removed without first disabling or dropping the dependent event notification. It can also occur if the assembly’s permission set (SAFE, EXTERNAL_ACCESS, UNSAFE) was changed in a way that invalidates the existing CLR trigger binding used by Service Broker event notifications.

Step-by-Step Resolution Guide

Step 1: Identify the Dependent Event Notification

  • Run the following query to find notifications tied to the assembly: SELECT name, type_desc, event_type_desc FROM sys.server_event_notifications UNION ALL SELECT name, type_desc, event_type_desc FROM sys.event_notifications;
  • Cross-reference the assembly name reported in the error message with SELECT * FROM sys.assemblies WHERE name = '<AssemblyName>';
  • Note the exact notification name and its scope (server-level or database-level) before proceeding.

Step 2: Drop and Recreate the Event Notification Safely

  • Execute DROP EVENT NOTIFICATION <NotificationName> ON SERVER; or ON DATABASE depending on scope.
  • Re-deploy the updated assembly using ALTER ASSEMBLY <AssemblyName> FROM 'C:\SQLAssemblies\<AssemblyName>.dll' WITH PERMISSION_SET = SAFE;
  • Recreate the event notification using CREATE EVENT NOTIFICATION <NotificationName> ON SERVER FOR <EventType> TO SERVICE '<ServiceName>', '<BrokerInstanceID>';

Step 3: Validate Assembly and Service Broker Configuration

  • Confirm Service Broker is enabled on the target database using SELECT is_broker_enabled FROM sys.databases WHERE name = '<DBName>'; and enable it with ALTER DATABASE <DBName> SET ENABLE_BROKER; if needed.
  • Check SQL Server error logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for related CLR load failures.
  • Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) to clear any cached CLR assembly state.

Step 4: Re-grant CLR and Assembly Permissions

  • Verify clr strict security and clr enabled configuration options using sp_configure 'clr enabled'; and sp_configure 'clr strict security';
  • If using UNSAFE or EXTERNAL_ACCESS assemblies, ensure the assembly is signed and the login has UNSAFE ASSEMBLY permission granted via GRANT UNSAFE ASSEMBLY TO [<Login>];
  • Reapply the assembly owner mapping with ALTER AUTHORIZATION ON ASSEMBLY::<AssemblyName> TO <SchemaOwner>;

Alternative or Verification Steps

  • Verify the fix by querying sys.event_notifications and sys.server_event_notifications again to confirm the notification is active and no longer flagged as dropped, then trigger a test event to confirm message delivery to the Service Broker queue.
  • If the error persists, consider fully uninstalling and reinstalling the assembly (DROP ASSEMBLY then CREATE ASSEMBLY) rather than using ALTER ASSEMBLY, as this forces SQL Server to rebuild all internal metadata links and dependency trees from scratch.