• Article

Troubleshooting Policy-Based Management Policies

Policy-Based Management records errors to both the Windows event log and, for scheduled policies, the SQL Server Agent log. If policies are not enabled or do not effect the expected target, the failure is not considered an error and is not logged.

Troubleshooting Policy Failures

This section describes two policy failures and the steps you can perform to troubleshoot them.

An On change: log Policy or On change: prevent Policy Is Not Executing

A policy that is using On change: log or On change_prevent evaluation mode is not effective for one of the following reasons:

  • The policy is disabled.

  • The target is excluded by a filter.

  • The target does not subscribe to the policy group that contains the policy.

  • For the On change: prevent evaluation mode, the Service Broker eventing mechanism is not monitoring the correct events.

  • There is a failure in the evaluation engine.

To troubleshoot this issue, follow these steps:

  1. Check whether the policy was executed. (This data can also be viewed through the log viewer.)

    1. To see whether the policy executed with exception messages, check the policy execution history in the msdb.dbo.syspolicy_policy_execution_history view.

    2. To see whether the policy executed for the specific target, check the policy execution history for the specific target in question in the msdb.dbo.syspolicy_policy_execution_history_details view.

  2. Check whether the policy is effective on the target in question.

    1. Verify the policy is enabled.

    2. To verify that the policy applies to the target, use the View Policies page in SQL Server Management Studio.

  3. For policies that use the On log: prevent evaluation mode, Service Broker checks whether the eventing mechanism is monitoring for the correct events.

    1. Verify that a transaction has committed and generated an event that is supported by the facet the condition of the policy is defined on.

    2. Verify that the Service Broker queue is monitoring for the correct events by using the following query:

      SELECT * FROM sys.server_event_notifications 
WHERE name = N'syspolicy_event_notification' ;
GO

  4. Check the evaluation engine.

    • Check the Windows event log for a Policy-Based Management error.

An On Schedule Policy Is Not Executing

An On Schedule policy can fail for the same reasons as an On change: log or On change: prevent policy, and also because there was a failure in the SQL Server Agent job. For more information about jobs, see Viewing and Modifying Jobs.

Troubleshooting Performance Problems

Policy-Based Management does not provide specific tools to diagnose performance related problems. To determine the execution time for policies, query the start_date and end_date columns in the msdb.dbo.syspolicy_policy_execution_history view.

Understanding Error Handling and Logging

This section describes how error handling and logging work for several policies.

On Change: Log Policy and On Change: Prevent Policy

Policy execution errors are logged to the Windows event log. The log will have the Policy-Based Management error text and an XML description that contains the details of the SQL Server event. Exceptions on the main execution logic are Policy execution errors are displayed in the msdb.dbo.syspolicy_policy_execution_history and msdb.dbo.syspolicy_policy_execution_history_details views.

On Schedule Policy

Because an On schedule policy involves SQL Server Agent, the Agent log files are the first location to look. Check whether the job has successfully completed. The SQL Server Agent log will contain failures of stored procedure calls. If the job completed successfully, check the Windows event log for policy execution failures.