Troubleshooting Guide: Error 9244 — Query Notification Cleanup Could Not Access Database Metadata

Why This Error Happens

Error 9244 occurs when SQL Server’s background cleanup task for Service Broker query notifications (Query Notification Cleanup Task) attempts to access system metadata for a database that is not currently in an ONLINE state. This typically happens when a database is in RESTORING, SUSPECT, OFFLINE, or RECOVERY_PENDING state, or when the database was dropped/detached while Service Broker queues still held active subscription references. It can also surface after an incomplete restore operation or a failover event where the metadata cache became stale relative to the actual database state.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database State

• Open SQL Server Management Studio (SSMS) or a query window and run:

  SELECT name, state_desc, is_broker_enabled 
  FROM sys.databases;

• Confirm whether the affected database shows ONLINE, RESTORING, SUSPECT, or OFFLINE in the state_desc column.
• Cross-reference the database ID (%d in the error) using:

  SELECT database_id, name FROM sys.databases WHERE database_id = <ID_from_error>;

Step 2: Complete or Repair the Restore Operation

• If the database is stuck in RESTORING, check if a restore sequence was left incomplete (missing WITH RECOVERY clause):

  RESTORE DATABASE [YourDB] WITH RECOVERY;

• If the database is SUSPECT, run DBCC CHECKDB to assess corruption:

  DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for corresponding restore failure messages.

Step 3: Disable and Re-enable Service Broker on the Database

• If the database is online but notifications are still failing, Service Broker’s internal GUID may be mismatched. Run:

  ALTER DATABASE [YourDB] SET DISABLE_BROKER;
  GO
  ALTER DATABASE [YourDB] SET ENABLE_BROKER;
  GO

• This forces SQL Server to refresh the Broker metadata and clear orphaned notification subscriptions tied to the old database state.

Step 4: Clear Orphaned Notification Queues

• Query the notification subscription table to identify stale entries:

  SELECT * FROM sys.dm_qn_subscriptions;

• If orphaned subscriptions reference the problematic database, restart the SQL Server service to force the Query Notification Cleanup Task to re-initialize against the corrected metadata:
• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER), right-click → Restart.

Step 5: Validate Permissions on System Metadata

• Ensure the SQL Server service account has not lost permissions on the master or msdb system databases (common after a Windows Server 2025 security policy update).
• Check via:

  SELECT * FROM fn_my_permissions('master', 'DATABASE');

• Re-grant VIEW SERVER STATE and CONNECT permissions if missing, using SQL Server Configuration Manager to confirm the service account identity under SQL Server Services properties.

Alternative or Verification Steps

• After applying fixes, re-run SELECT name, state_desc FROM sys.databases; and confirm the database reports ONLINE; monitor the SQL Server error log for 30 minutes to confirm Error 9244 no longer recurs.
• If the issue persists, as a fallback, perform a full database backup, then drop and restore the database from a known-good backup file to rebuild clean Service Broker metadata from scratch.