Setting commitbatchsize returns ‘An invalid parameter or value was specified in the command line.’

Recently worked on a case where customer was getting this error when modifing the Distribution agent Job to use CommitBatchSize of 12K and above:

Agent message code 20028. An invalid parameter or value was specified in the command line. (parameter = ‘-commitbatchsize’, value = ‘12000’)

This error occurs when setting commitbatchsize greater than 10000. The table “MSDB..Msagentparameterlist” created on the Distributor when a SQL Server is enabled for Distribution contains the minimum and maximum values defined for all parameters used by different agents.

To see all values for the Distribution Agent use:

SELECT *
FROM msdb..MSagentparameterlist
WHERE agent_type = 3 --Distribution agent

To check what is the max value for CommitBatchSize you can use this query:

SELECT *
FROM msdb..MSagentparameterlist
WHERE agent_type = 3 --Distribution agent
AND parameter_name = 'CommitBatchSize'

Taiyeb Zakir, Microsoft SQL Escalation Services