syspolicy_purge_history step 3 fails System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

Question

SQL Server 2016 SP2; Windows Server 2019.
After the OS was upgraded to Windows Server 2019, The system created job fails with the following error in the history:

Log Job History (syspolicy_purge_history)

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

Message
Executed as user: Wxxxx\sql_rexxx2. Unhandled Exception: System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list. at System.Text.StringBuilder.AppendFormatHelper(IFormatProvider provider, String format, ParamsArray args) at System.String.FormatHelper(IFormatProvider provider, String format, ParamsArray args) at System.String.Format(IFormatProvider provider, String format, Object[] args) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.GetErrorRecordMessage(ErrorRecord errorRecord) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.HandleAgentJob(RunspaceConfiguration config) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.Main(String[] args). Process Exit Code -532462766. The step failed.

What is causing this error and how do I fix it?
What additional information is needed to work on this?

Answer 1

Hi,

I think the failure of the third step of the syspolicy_purge_history job should be related to your system upgrade. It is recommended to try the following script to recreate this job or repair SQL Server.

DECLARE @jobId uniqueidentifier
-- Obtain the current job identifier that is associated with the PurgeHistory
SELECT @jobId = CAST(current_value AS uniqueidentifier)
FROM msdb.dbo.syspolicy_configuration_internal
WHERE name = N'PurgeHistoryJobGuid'

-- Delete the job identifier association in the syspolicy configuration

DELETE FROM msdb.dbo.syspolicy_configuration_internal
WHERE name = N'PurgeHistoryJobGuid'

-- Delete the offending job
EXEC msdb.dbo.sp_delete_job @job_id = @jobId

-- Re-create the job and its association in the syspolicy configuration table
EXEC msdb.dbo.sp_syspolicy_create_purge_job

If you are not using policies in SQL Server, you can disable the job.

Answer 2

Criszhan-msft, I ran your script above. It recreated the job, but is fails the same.
I checked the version of powershell on the failing server compared to a server where the job runs without errors.
Failing server :
Major Minor Build Revision

---

5 1 17763 1971

Running server :
Major Minor Build Revision

---

5 1 14393 4530

Here is the error message:

Date 08/03/2021 11:52:01 AM
Log Job History (syspolicy_purge_history)

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

Message
Executed as user: WxxxxA\sql_rxxxxv2. Unhandled Exception: System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list. at System.Text.StringBuilder.AppendFormatHelper(IFormatProvider provider, String format, ParamsArray args) at System.String.FormatHelper(IFormatProvider provider, String format, ParamsArray args) at System.String.Format(IFormatProvider provider, String format, Object[] args) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.GetErrorRecordMessage(ErrorRecord errorRecord) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.HandleAgentJob(RunspaceConfiguration config) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.Main(String[] args). Process Exit Code -532462766. The step failed.