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

Share this Post

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


Share this Post

About: ReplTalk


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.