Partager via

Policy History Table Exception Columns

  • Article

I was recently asked what the exception_message and exception columns in the msdb.dbo.syspolicy_policy_execution_history_internal table are used for.

There are situations where the policy evaluate can throw an exception. For example, if the policy references a property in a facet which is not available on the given version or edition of the instance. For example the “Server Configuration” facet contains properties that only apply to the Express edition of SQL Server: UserInstancesEnabled and UserInstanceTimeout. If your policy references a condition with these properties the evaluation will throw an exception.

The exception message column contains the friendly exception message. In the example above it’ll contain:

Property value 'UserInstancesEnabled' is not available.

The exception column contains the full stack. In the example above it’ll contain:

Microsoft.SqlServer.Management.Dmf.NonRetrievablePropertyException: Property value 'UserInstancesEnabled' is not available. ---> Microsoft.SqlServer.Management.Smo.UnsupportedFeatureException: UserInstancesEnabled is not supported on this edition of SQL Server. at Microsoft.SqlServer.Management.Smo.Configuration.get_UserInstancesEnabled() at Microsoft.SqlServer.Management.Smo.ServerAdapterBase.get_UserInstancesEnabled() --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Dmf.Condition.Evaluate(Object target) at Microsoft.SqlServer.Management.Dmf.ObjectSet.CalculateTargets(IEnumerable objectSet, Condition condition, Object[]& conforming, TargetEvaluation[]& violating) at Microsoft.SqlServer.Management.Dmf.ObjectSet.CalculateTargets(SqlStoreConnection targetConnection, Condition condition, String policyCategory, Object[]& conforming, TargetEvaluation[]& violating) at Microsoft.SqlServer.Management.Dmf.Policy.EvaluatePolicyUsingConnections(AdHocPolicyEvaluationMode evaluationMode, SfcQueryExpression targetQueryExpression, ISfcConnection[] targetConnections)

I hope this clears up what these columns are for. Remember, only policies that are enabled on the server have their evaluation logged. If the policy is configured to only be evaluated on demand it's results are not written to the history table.