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.

Soranno, Bill 1 Reputation point
2021-08-02T19:40:14.637+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,912 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Cris Zhan-MSFT 6,611 Reputation points
    2021-08-03T03:42:54.187+00:00

    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.

    0 comments No comments

  2. Soranno, Bill 1 Reputation point
    2021-08-03T17:11:37.727+00:00

    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.