Of course, policies would be
purely academic if they never actually executed and evaluated any
objects. Policies may be set for On Schedule, On Demand, On Change, or
Log Only. Policies must be enabled using their context menu. On demand
policies may not be enabled.
For policies set to On Demand, the policies may
be evaluated for any object using the object's context menu → Policies
→ Evaluate. This opens the Evaluate Polices dialog for the object, as
shown in Figure 1.
Figure 1 All the policies that can be run on-demand for an object can be selected and evaluated using the Evaluate Polices dialog.
The outcome of the policy evaluations is displayed in the Evaluation Results page, as seen in Figure 2.
Figure 2 Here the Aesop database passes the No AutoShrinkage policy and declares to be in good health.
You can see the current state of any object for
all policies regardless of their Execution mode by right-clicking on a
policy and seeing the Enabled or Disabled options in the context menu.
Within the View Policies dialog, you can see the
history for any policy evaluation for the object using the log viewer
by clicking the view history link in the policy row. You can also see
the policy evaluation in the Windows event log and the SQL Server event
To view a history of policy execution query use the following:
SELECT * FROM msdb.dbo.syspolicy_policy_execution_history_details;
To view exception query use the following:
SELECT * FROM msdb.dbo.syspolicy_policy_execution_history;
PBM health is also well integrated into
Management Studio's object listings. The Object Explorer Details page
has an optional column to display the Policy Health of any object.
PBM requires SQL Server Agent
running on the central monitoring server. Be sure the SQL Server Agent
service is set to start automatically and that its service account
password won't expire.