2

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.

Improve this question
4
  • Don't suppose the machine in question is running in a failover cluster is it?
    – Nic
    Jan 6, 2017 at 17:48
  • 1
    ...to wit, support.microsoft.com/en-us/kb/955726 ... though I'm curious why deleting these rows from the policy history tables needs to go out to Powershell in the first place. I can understand why there would be a Powershell method for this, but can't fathom why SQL Server Agent would need to use it. Under the covers it should simply be calling a stored procedure (though on first glance I couldn't determine which one).
    – Aaron Bertrand
    Jan 6, 2017 at 17:59
  • 1
    A few questions: (1) what Nic asked. (2) Is the server name and instance name correct in the link? See Error section "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." (3) Is the PSModulePath set correctly in the environment? (4) Permissions: Does jonbrown have all of the needed permissions? (don't forget file path permissions)
    – Laughing Vergil
    Jan 6, 2017 at 18:45
  • To answer Nic's question yes. and for the questions asked by Laughing Vergil. (2) from what I know it should be fine cause it was working all along. (3) yes PSModulePath is set correctly in the environment. (4) Yes
    – Kefash
    Jan 6, 2017 at 19:10

2 Answers 2

Reset to default
1

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.

Improve this answer
1
  • The scope and execution policy in both the Windows PowerShell and the SQL PowerShell consoles are set to the above and the job is still failing. Any other recommendation?
    – Kefash
    Jan 10, 2017 at 14:07
0

I know this was a question that I asked long ago and the issue was resolved. However, I realized that it is still being viewed so I will just state what I did to resolve the issue.

I opened the SQL Configuration Manager and added the alias as my instance name got updated. After that the connection was successful and the job completed without error.

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.