Edit

syspolicy_policy_execution_history_details (Transact-SQL)

  • Article

Applies to: SQL Server

Displays the condition expressions that were executed, the targets of the expressions, the result of each execution, and details about errors if any occurred. The following table describes the columns in the syspolicy_execution_history_details view.

Column name Data type Description
detail_id bigint Identifier of this record. Each record represents the attempt to evaluate or enforce one condition expression in a policy. If applied to multiple targets, each condition will have a detail record for each target.
history_id bigint Identifier of the history event. Each history event represents one try to execute a policy. Because a condition can have several condition expressions and several targets, a history_id can create several detail records. Use the history_id column to join this view to the syspolicy_policy_execution_history view.
target_query_expression nvarchar(max) Target of the policy and syspolicy_policy_execution_history view.
execution_date datetime Date and time that this detail record was created.
result bit Success or failure of this target and condition expression evaluation:

0 (success) or 1 (failure).
result_detail nvarchar(max) Result message. Only available if provided by the facet.
exception_message nvarchar(max) Message generated by the exception if one occurred.
exception nvarchar(max) Description of the exception if one occurred.

Remarks

When you're troubleshooting Policy-Based Management, query the syspolicy_policy_execution_history_details view to determine which target and condition expression combinations failed, when they failed, and review related errors.

The following query combines the syspolicy_policy_execution_history_details view with the syspolicy_policy_execution_history_details and syspolicy_policies views to display the name of the policy, the name of the condition, and details about failures.

SELECT Pol.name AS Policy,   
Cond.name AS Condition,   
PolHistDet.target_query_expression,   
PolHistDet.execution_date,   
PolHistDet.result,   
PolHistDet.result_detail,   
PolHistDet.exception_message,   
PolHistDet.exception   
FROM msdb.dbo.syspolicy_policies AS Pol  
JOIN msdb.dbo.syspolicy_conditions AS Cond  
    ON Pol.condition_id = Cond.condition_id  
JOIN msdb.dbo.syspolicy_policy_execution_history AS PolHist  
    ON Pol.policy_id = PolHist.policy_id  
JOIN msdb.dbo.syspolicy_policy_execution_history_details AS PolHistDet  
    ON PolHist.history_id = PolHistDet.history_id  
WHERE PolHistDet.result = 0 ;

Permissions

Requires membership in the PolicyAdministratorRole role in the msdb database.

Related content

Administer Servers by Using Policy-Based Management
Policy-Based Management Views (Transact-SQL)