Since SQL Server 2008, Microsoft has introduced a system job called “syspolicy_purge_history”. This job is installed and enabled by default, and it contains three steps scheduled at 02:00 AM:
What is this job? And what is it for?
Since SQL Server 2008, a new feature called Policy Based Management has been added. When your policies are run, the results are stored in the msdb. But without a purge mechanism, msdb will keep growing. So Microsoft introduced the famous system job named “syspolicy_purge_history” to clean the results older than the days defined in the “HistoryRetentionInDays” property of Policy Management.
Should I disable it?
Definitively not. This job is part the well-functioning of SQL Server.
Should I care about it?
We do recommend to monitor this job as all other dba jobs.
But last time, this system job failed after each automatic and/or manual executions…
If you go in details, you notice the third steps failed at the following line: “set –executionpolicy RemoteSigned –scope process –Force”
Apparently, the SQL Server engine was not able to modify the execution policy to “RemoteSigned”. Does the engine have enough permissions? Should I modify manually the execution policy as Administrator?
Let’s see the current “ExecutionPolicy” configuration for the SQL Server PowerShell. Open the console from SQL Server Management Studio:
And you have a beautiful error to begin softly:
PowerShell executed the same command as previously, but with the same success…
Hopefully we have more details: Windows PowerShell tried to change the “ExecutionPolicy” parameter, but a policy have overridden the change…
Let’s see the “ExecutionPolicy” depending on the different scopes:
The “ExecutionPolicy” at the “MachinePolicy” scope is set to “AllSigned” (which is more restrictive), and may override the configuration at the “Process” scope. By setting the “ExecutionPolicy” to “RemoteSigned” at the “MachinePolicy” scope, the problem may be resolved…
…or not! We cannot change manually the setting, even if the console is opened as Administrator!
But as you can see, we have more details: we cannot change the “ExecutionPolicy” this way, but we must change it through Group Policy.
Let’s open Local Group Policy Editor, and let’s browse to “Local Computer Policy\Computer Configuration\Administrative Templates\Windows Components\Windows PowerShell”.
But no policy is configured…
If I cannot change the “ExecutionPolicy” at the “MachinePolicy” scope through the SQL Server PowerShell console, I propose you to change it directly in the registry
…the policy is correctly set to “RemoteSigned”, but this value is overridden from somewhere else… which is apparently not locally because I found no GPO configured… Someone must have set a GPO for the “ExecutionPolicy” on the Domain Controller…
And indeed, there is a GPO which affects all the servers of the organization:
For this resolution, Microsoft proposes two workarounds in the KB2995870:
- Create a New Organization Unit for this server…
- Or simply disable this GPO…
To be honest, the domain administrator does not want to create a New Organization Unit for only one server. But he also does not want to disable this GPO for all the servers of the Organization.
Hopefully, it does not mean we are stuck Even if the Active Directory is able to redefine the Policy on all the servers in an Organization, the local computer can always have the last word!
I force the configuration of the “ExecutionPolicy” policy directly in the local registry where SQL Server is installed:
And the miracle happens:
To conclude, I will simply and shortly say: Registry is my best friend