Policy Evaluation

Overview

On most instances the honest answer here is one line saying policy based management is not in use, and that is worth a page of its own for the instances where it is:

  • A policy evaluating on a schedule is a scheduled workload nobody has counted.
  • Its results are kept in a table nobody purges.
  • A policy that fails is silent unless somebody wired an operator to the job behind it.
The Policy Evaluation page: whether policy based management is doing anything here
The whole page. Sixteen policies, none enabled and none ever evaluated, which is the shipped state of an instance that has never used the feature.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Policy Evaluation.

Shown only on SQL Server 2008 and newer, which is when policy based management arrived.

Four things decide whether a policy does anything, and all four are on the page

Enabled

A disabled policy is evaluated by nothing.

The evaluation mode

Mode What it means
On demand It runs when a person runs it, which on an unattended server means never.
On schedule The only mode with a job behind it, and that job is the one worth looking at.
On change, prevent Enforced by a DDL trigger at the moment of the change.
On change, log only Recorded at the moment of the change and nothing more.

The last evaluation

syspolicy_execution_history records every run with the exception text when one threw, which is the only place that error is written down.

The health state

The current pass or fail per target rather than per policy. A policy that passes on forty databases and fails on one is passing as far as the history is concerned, and failing where it matters.

Reading the chart

The bars: failing targets where there are any, evaluations where there are not
The mode, the enabled state and the condition sit under each name. Every policy here is on demand and disabled, which is why every bar is empty.

One bar per policy. Where a policy has failing targets the bar is that count, and where it has none the bar is the evaluations in its history, so the picture is of whichever of the two there is something to say about.

The line under the header is the answer on most instances: these are evaluated on demand, which on an unattended server means never.

The bars draw the top thirteen of the sixteen. The grid holds them all.

Reading the grid

The grid: mode, enabled, runs, last run and failing targets
Evaluated and Enabled decide whether anything happens at all. Failing targets is the column where a policy that passes overall can still be failing on the one database that matters.
Column What it is
Policy The policy.
Condition The condition it evaluates.
Evaluated The evaluation mode.
Enabled Whether it is switched on.
Runs Executions in the history.
Last run The most recent one, with its result.
Failing targets How many targets are currently in a failed health state.
Verdict Whether this policy is evaluated at all, and what it last found.

The purge job is checked too

A failing syspolicy_purge_history is one of the few ways msdb grows without anybody choosing it, and msdb Space and Retention sees the consequence without being able to name the cause.

This page names it: the job, whether it is enabled, when it last ran and whether it succeeded.

The toolbar

Control What it does
Space and retention Opens msdb Space and Retention.
Step failures Opens Job Step Failures, where the purge job’s failure shows.

Where the data comes from

Source What it gives
dbo.syspolicy_policies The policies, their condition, evaluation mode and enabled state.
dbo.syspolicy_conditions The condition expressions.
dbo.syspolicy_execution_history Every evaluation, its result and its exception text.
dbo.syspolicy_execution_history_details The per target detail.
dbo.syspolicy_system_health_state The current pass or fail per target.
dbo.sysjobs The schedule job behind an on-schedule policy, and the purge job.

Messages you may see

Policy based management is not in use on this instance. Which is the ordinary answer, and is one line rather than an empty grid.

Policy based management arrived in SQL Server 2008. This instance is older, so the syspolicy views this page reads are not there at all.

Report Why you would go there
msdb Space and Retention What the policy history weighs when the purge is failing.
Job Step Failures The purge job, and the policy evaluation job.
Agent Activity Whether those jobs are running at all.

Frequently asked questions

The policy passes and something is still wrong. Look at Failing targets. The history result is a single verdict for the whole evaluation; the health state is per target and is where one failing database shows.

We have policies and none of them have ever run. Check the evaluation mode. On demand means a person has to run it, and on a server nobody logs into that is never.

Why does this page care about a purge job? Because a failing syspolicy_purge_history fills msdb and nothing else on the instance connects the two. This page can name the cause; the space page can only show the effect.