syspolicy_policy_execution_history_details (Transact-SQL)


Updated: August 9, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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 nameData typeDescription
detail_idbigintIdentifier 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_idbigintIdentifier 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_expressionnvarchar(max)Target of the policy and syspolicy_policy_execution_history view.
execution_datedatetimeDate and time that this detail record was created.
resultbitSuccess or failure of this target and condition expression evaluation:

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

When you are 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 AS Policy, AS Condition,   
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 ;  

Requires membership in the PolicyAdministratorRole role in the msdb database.

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

Community Additions