syspolicy_event_queue disabled automatically

Hammi Amine 0 Reputation points
2024-04-18T08:58:26.8+00:00

The msdb service broker queue syspolicy_event_queue is disabled automatically, messages are not processed and keep growing until taking full drive space. Here is the error message from Log File Viewer :

I have sql server 2022, CU12. I have executed the repair process but didn't resolve the issue.

I have also found this solution:

EXECUTE AS USER = '##MS_PolicyEventProcessingLogin##'  GO EXEC dbo.sp_syspolicy_events_reader GO

It worked well and emptied the messages. But then, the queue still disabled.

Please advice.

Message Error number 6549 was encountered while processing an event. The error message is: A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_execute_policy': System.Data.SqlClient.SqlException: Only System Administrator can specify WITH LOG option for RAISERROR command. System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SqlServer.Management.Dmf.PolicyEvaluationHelper.LogExceptionMessage(String message, SqlConnection connection) at Microsoft.SqlServer.Management.Dmf.PolicyEvaluationHelper.EvaluateAutomatedPolicy(String policy, SqlXml eventData, Int64& historyId) at Microsoft.SqlServer.Management.Dmf.PolicyEvaluationWrapper.EvaluateAutomatedPolicy(String policy, SqlXml eventData, Int64& historyId) . User transaction, if any, will be rolled back..

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,738 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. LucyChen-MSFT 965 Reputation points Microsoft Vendor
    2024-04-19T02:23:04.33+00:00

    Hi @Hammi Amine,

    Thanks for your information.

    I noticed this error information:

    System.Data.SqlClient.SqlException: Only System Administrator can specify WITH LOG option for RAISERROR command.

    This means you don't have permission to use the WITH LOG option. Please look at this article. You need to be member of the sysadmin fixed server role or users with ALTER TRACE permissions.

    In addition, from the solution you've used:

    EXECUTE AS USER = '##MS_PolicyEventProcessingLogin##'  GO EXEC dbo.sp_syspolicy_events_reader GO

    I think missing logins will cause the issue you meet.

    Could you please post what do the two SELECT return? Thanks for your understanding.

    SELECT * FROM sys.server_principals WHERE name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##')
SELECT * FROM msdb.sys.database_principals WHERE name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##')

    Regards,

    Lucy Chen

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    https://docs.microsoft.com/en-us/answers/support/email-notifications