syspolicy_purge_history job getting failed (SQL Server 2012)

Question

Job Name  syspolicy_purge_history
Step Name  Erase Phantom System Health Records.
Duration  00:00:05
Sql Severity 0
Sql Message ID 0
Operator Emailed 
Operator Net sent 
Operator Paged 
Retries Attempted 0

Error Message :-

A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS  -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The following error occurred while loading the extended type data file:
Microsoft.PowerShell, D:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\sqlprovider.types.ps1xml : File skipped because of the following validation exception: AuthorizationManager check failed..

Answer 1

Hi nap_bhatt,

Based on your description, we need to verify that if you get the error while starting the syspolicy_purge_history job manually in SQL Server 2012. If so, which account do you use to log on to the SQL Server instance and run the job?

According to the error message “AuthorizationManager check failed” , it indicates that your account has no permission to execute the PowerShell script in SQL Server Agent job. To troubleshoot this issue, please make sure that your account has permissions to the SQLPS folder and it has rights to execute the PowerShell script.

In addition, please use the “Get-ExecutionPolicy” PowerShell command to check the current status of execution policy, if the execution policy is set to “Restricted”, try to set it to “RemoteSigned” or “Unrestricted”. Please note that you need to set this execution policy in both the Windows PowerShell  and the SQL PowerShell consoles.

For more information about syspolicy_purge_history job in SQL Server 2012,  please review this similar blog: SQL job “syspolicy_purge_history” fails on SQL 2012 .

 
Thanks,
Lydia Zhang

Answer 2

Hi Lydia

             Thank You very much for your reply. i would check you have mentioned and let see what outcomeis coming. I will update you

    

Answer 3

Hi nap_bhatt,

I’m writing to follow up with you on this post. Was the problem resolved after performing our action plan steps? If you are satisfied with our solution, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.

Thanks,
Lydia Zhang

Answer 4

Hey Lydia

              Your steps are really help me i did it that way but still job is gettign failed at step #3. powershell is not recognised powersehll commands.

Thank You

Nirav Bhatt

Answer 5

Can you try the below link

http://www.orcsweb.com/blog/jamie-furr/sql-job-syspolicy_purge_history-fails-on-sql-2012/

--Prashanth

Answer 6

Hi Lydia,

I had the same problem with a SQLServer 2014 and created a workaround. You can find it here. Would be nice if you could have an eye on it.

---

Regards Jörg

Answer 7

Hi nap_bhatt

Check if the following registry keys exist:

For SQL 2012
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\Shellds\Microsoft.SqlServer.Management.PowerShell.sqlps110
ExecutionPolicy REG_SZ RemoteSigned
Path REG_SZ C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\SQLPS.exe

For SQL 2014

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\Shellds\Microsoft.SqlServer.Management.PowerShell.sqlps120
ExecutionPolicy REG_SZ RemoteSigned
Path REG_SZ C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\SQLPS.exe

Regards
Corsus Piratus