Sql-server – SQL Server 2014 “syspolicy_purge_history” job keeps failing

jobssql serversql server 2014ssms

I am using SQL Server 2014 and the job "syspolicy_purge_history" keeps failing at step 3 "Erase Phantom System Health Records" and its driving me crazy as I don't know what is causing the failure.

here is the command:

if ('$(ESCAPE_SQUOTE(INST))' -eq 'MSSQLSERVER') {$a = '\DEFAULT'} ELSE {$a = ''};
(Get-Item SQLSERVER:\SQLPolicy\$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()

The error message I got was:

Executed as user: jonbrown. A job step received an error at line 2 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\xyz\T$a).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Failed to connect to server . A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) '. Process Exit Code -1. The step failed.

Best Answer

Can you try this in the Windows Server PowerShell path?

PS C:\Windows\system32> Get-ExecutionPolicy -list

     Scope            ExecutionPolicy
     --------         ------------------
     MachinePolicy    Undefined
     UserPolicy       Undefined
     Process          Undefined
     CurrentUser      Undefined
     LocalMachine     Restricted

..and then the same in the SQL Server path:

PS SQLSERVER:\> Get-ExecutionPolicy -list

     Scope            ExecutionPolicy
     --------         ------------------
     MachinePolicy    Undefined
     UserPolicy       Undefined
     Process          RemoteSigned
     CurrentUser      Undefined
     LocalMachine     Restricted

If the execution policy is set to “Restricted”, try to set it to “RemoteSigned” or “Unrestricted”. 

Set-ExecutionPolicy -Scope LocalMachine -ExecutionPolicy Unrestricted

or 

Set-ExecutionPolicy -Scope LocalMachine -ExecutionPolicy RemoteSigned

Please note that you need to set this execution policy in both the Windows PowerShell and the SQL PowerShell consoles.

Related Question