Sql-server – SQL Server Jobs – syspolicy_purge_history fails

jobssql serversql-server-2016

The job syspolicy_purge_history fails at the 3rd step. The error that I get is the following one:

Executed as user: UserABC.
A job step received an error at line 1 in a PowerShell script.
The corresponding line is 'set-executionpolicy RemoteSigned -scope process -Force'.
Correct the script and reschedule the job.
The error information returned by PowerShell is: 'Security error. '. Process Exit Code -1. The step failed.

Was wondering if you guys have faced this before and how to go about fixing this.

Best Answer

Thanks to the comment of @Tony Hinkle. I was able to resolve the issue based on the comment at the bottom of this thread.

SQL Server 2012 syspolicy_purge_history fails step 3 "Erase Phantom System Health Records."

Under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps130 I changed the key ExecutionPolicy value to Unrestricted.

I did not make any changes to keys under HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqltoolsps130.

Related Solutions

Sql-server – Powershell-type SQL Agent Job failing intermittently trying to get Disk Space on Server via WMI-query

Don't know if you're still looking for a solution to this but I'd change the Powershell for the insert command just a little. Add this after the line setting $size = $_.Size

   $sql = "INSERT dbo.DiskSpace (drive, [free(bytes)], [total(bytes)]) VALUES ('$Name', $FreeSpace, $Size)"
   $sql | Out-File "C:\tmp\debug.sql" –Append # to wherever is appropriate for your system
   $cmd.CommandText = $sql

To me using the extra variable but getting an easier to read insert command is a good tradeoff and the debug.sql file makes finding sql errors much easier. HTH

Sql-server – Syspolicy_purge_history connection issue

You will need to put the port number in there. When using the default instance the SQL Browser is not contacted in order to get the port number to use to connect. Because of this you have to specify the port number when connecting otherwise the OLE/ODBC driver doesn't know what TCP port number to connect to.

Please clarify how you took a standalone server and made it a cluster. That isn't supported unless you uninstalled SQL and reinstalled it.

Best Answer

Related Solutions

Sql-server – Powershell-type SQL Agent Job failing intermittently trying to get Disk Space on Server via WMI-query

Sql-server – Syspolicy_purge_history connection issue

Related Question