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.
- msdb.dbo. syspolicy_target_sets
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.
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.
/* What policies exist on the server? */
SELECT p.name AS ‘policy_name’,
c.name AS ‘condition_name’,
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’,
FROM msdb.dbo.syspolicy_conditions c
ORDER BY c.name
/* What categories and subscriptions exist on the server? */
/* What is the execution history of policy violations */
pp.name AS ‘policy_name’,
Datediff(ss, h.start_date, h.end_date) AS ‘exec_time_ss’,
CAST(d.result_detail AS XML) AS ‘result_detail’,
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? */
p.name AS ‘policy_name’,
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,
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.