Vinod Kumar (Blog home)

Policy Management is an interesting topic and lesser known when we start drilling into the internals and the implementation. In this post let me walk through some of the objects that you might want to keep an eye on msdb and various other DBs when creating, managing and listing Policy Based Management. Let us start with Policy History part -

When a policy executes it stores information on its execution history in the syspolicy tables in msdb. Under the management folder under the policies folder, you can right click a policy and “View History” for a policy or a group of policies. This will provide information on execution date, policy name, targets, details and messages.

• The execution date column is the date and time that the policy started execution.
• The policy name column tells you which policy was executing.
• The target column tells you which targets the condition of the policy were evaluated against.
• The details column is an xml representation of the expected value versus the actual value of the condition for the target.

The target and detail information is only displayed when the policy has a violation.

• The message column will display information on exceptions that occurred when the policy is executed.
• If the Check On Change (prevent out of compliance) rolls back the transaction to prevent the out of compliance no record is displayed.

The Policy History uses the following views to obtain this information.

1. msdb.dbo.syspolicy_policies
2. msdb.dbo.syspolicy_conditions
3. syspolicy_policy_categories
4. msdb.dbo. syspolicy_target_sets
5. msdb.dbo.syspolicy_policy_execution_history
6. msdb.dbo.syspolicy_policy_execution_history_details

Syspolicy Tables and Views in MSDB Database

All metadata for Policy Based Management is stored in tables in the msdb database. There are tables to store information on facets, conditions, policies, targets, subscriptions, health state, and execution history.

The syspolicy_execution_internal table has an instead of trigger for inserts (syspolicy_execution_trigger). Within the trigger, sp_syspolicy_execute_policy is executed.

Tables

1. syspolicy_management_facets
2. syspolicy_facet_events
3. syspolicy_conditions_internal
4. syspolicy_policy_groups_internal
5. syspolicy_policies_internal
6. syspolicy_target_sets_internal
7. syspolicy_target_sets_levels_internal
8. syspolicy_policy_group_subscriptions_internal
9. syspolicy_system_health_state_internal
10. syspolicy_policy_execution_history_internal
11. syspolicy_policy_execution_history_details_internal
12. syspolicy_execution_internal

The syspolicy_policy_health_state and syspolicy_policy_execution_history views can be used to see information on failed policy violations. The syspolicy_policy_execution_history has columns on start_date and end_date for a policy execution. You can use a datediff function to determine the length of time for a policy execution. There is also information on the target, target expression, condition results. Below are some sample queries that you can use to pull information out of the views and tables.

Views

1. syspolicy_conditions
2. syspolicy_policy_groups
3. syspolicy_policies
4. syspolicy_target_sets
5. syspolicy_target_sets_levels
6. syspolicy_policy_group_subscriptions
7. syspolicy_system_health_state
8. syspolicy_policy_execution_history
9. syspolicy_policy_execution_history_details

Sample Queries

/* What policies exist on the server? */

SELECT p.name AS ‘policy_name’,
       p.policy_id,
       p.is_enabled,
       p.execution_mode,
       c.name AS ‘condition_name’,
       c.facet,
       p.date_created,
       p.created_by,
       p.modified_by,
       p.date_modified
FROM   msdb.dbo.syspolicy_policies p
       INNER JOIN msdb.dbo.syspolicy_conditions c
         ON p.condition_id = c.condition_id
ORDER  BY p.name 

/* What conditions exist on the server? */

SELECT c.name AS ‘condition_name’,
       c.condition_id,
       c.facet,
       c.DESCRIPTION,
       c.expression,
       c.date_created,
       c.created_by,
       c.modified_by
FROM   msdb.dbo.syspolicy_conditions c
ORDER  BY c.name 

/* What categories and subscriptions exist on the server? */

SELECT *
FROM   msdb.dbo.syspolicy_policy_categories

SELECT *
FROM   msdb.dbo.syspolicy_policy_category_subscriptions 

/* What is the execution history of policy violations */

SELECT h.history_id,
       pp.name                                AS ‘policy_name’,
       pp.policy_id,
       pp.execution_mode,
       h.result,
       h.start_date,
       h.end_date,
       Datediff(ss, h.start_date, h.end_date) AS ‘exec_time_ss’,
       d.target_query_expression,
       CAST(d.result_detail AS XML)           AS ‘result_detail’,
       d.EXCEPTION,
       d.exception_message
FROM   msdb.dbo.syspolicy_policies AS pp
       INNER JOIN msdb.dbo.syspolicy_policy_execution_history AS h
         ON h.policy_id = pp.policy_id
       INNER JOIN msdb.dbo.syspolicy_policy_execution_history_details AS d
         ON d.history_id = h.history_id
ORDER  BY h.history_id 

/* What exceptions have occurred? */

SELECT h.history_id,
       p.name AS ‘policy_name’,
       p.policy_id,
       h.start_date,
       h.end_date,
       h.result,
       h.EXCEPTION,
       h.exception_message
FROM   msdb.dbo.syspolicy_policies AS p
       INNER JOIN msdb.dbo.syspolicy_policy_execution_history h
         ON p.policy_id = h.policy_id
WHERE  Datalength(h.EXCEPTION) > 1
ORDER  BY h.start_date DESC,
          h.end_date DESC 

Cleaning up syspolicy execution history tables

Most of the times when Policy Based Management is created manually, lot of DBAs find it difficult to delete the policies made via this mechanism. There is a stored procedure called sp_syspolicy_delete_policy_execution_history. This stored procedure can be used to delete records from the syspolicy_policy_execution_history_internal table either by date or policy id.