Edit

sp_change_agent_parameter (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Managed Instance

Changes a parameter of a replication agent profile stored in the MSagent_parameters system table. This stored procedure is executed at the Distributor where the agent is running, on any database.

Transact-SQL syntax conventions

Syntax

  
sp_change_agent_parameter [ @profile_id= ] profile_id, [ @parameter_name= ] 'parameter_name', [ @parameter_value= ] 'parameter_value'

Arguments

[ @profile_id = ] profile_id, Is the ID of the profile. profile_id is int, with no default.

[ @parameter_name = ] 'parameter_name' Is the name of the parameter. parameter_name is sysname, with no default. For system profiles, the parameters that can be changed depend on the type of agent. To find out what type of agent this profile_id represents, locate the profile_id column in the Msagent_profiles table, and note the agent_type value.

Note

If a parameter is supported for a given agent_type, but has not been defined in the agent profile, an error is returned. To add a parameter to an agent profile you must execute sp_add_agent_parameter.

For a Snapshot Agent (agent_type=1), if defined in the profile, the following properties can be changed:

  • 70Subscribers

  • BcpBatchSize

  • HistoryVerboseLevel

  • LoginTimeout

  • MaxBcpThreads

  • MaxNetworkOptimization

  • Output

  • OutputVerboseLevel

  • PacketSize

  • QueryTimeout

  • StartQueueTimeout

  • UsePerArticleContentsView

For a Log Reader Agent (agent_type=2), if defined in the profile, the following properties can be changed:

  • HistoryVerboseLevel

  • LoginTimeout

  • MessageInterval

  • Output

  • OutputVerboseLevel

  • PacketSize

  • PollingInterval

  • QueryTimeout

  • ReadBatchSize

  • ReadBatchThreshold

For a Distribution Agent (agent_type=3), if defined in the profile, the following properties can be changed:

  • BcpBatchSize

  • CommitBatchSize

  • CommitBatchThreshold

  • FileTransferType

  • HistoryVerboseLevel

  • KeepAliveMessageInterval

  • LoginTimeout

  • MaxBcpThreads

  • MaxDeliveredTransactions

  • MessageInterval

  • Output

  • OutputVerboseLevel

  • PacketSize

  • PollingInterval

  • QueryTimeout

  • QuotedIdentifier

  • SkipErrors

  • TransactionsPerHistory

For a Merge Agent (agent_type=4), if defined in the profile, the following properties can be changed:

  • AltSnapshotFolder

  • BcpBatchSize

  • ChangesPerHistory

  • DestThreads

  • DownloadGenerationsPerBatch

  • DownloadReadChangesPerBatch

  • DownloadWriteChangesPerBatch

  • DynamicSnapshotLocation

  • ExchangeType

  • FastRowCount

  • FileTransferType

  • GenerationChangeThreshold

  • HistoryVerboseLevel

  • InputMessageFile

  • InteractiveResolution

  • InterruptOnMessagePattern

  • KeepAliveMessageInterval

  • LoginTimeout

  • MaxBcpThreads

  • MaxDownloadChanges

  • MaxUploadChanges

  • MetadataRetentionCleanup

  • NumDeadlockRetries

  • Output

  • OutputMessageFile

  • OutputVerboseLevel

  • PacketSize

  • ParallelUploadDownload

  • PauseOnMessagePattern

  • PauseTime

  • PollingInterval

  • ProcessMessagesAtPublisher

  • ProcessMessagesAtSubscriber

  • QueryTimeout

  • QueueSizeMultiplier

  • SrcThreads

  • StartQueueTimeout

  • SyncToAlternate

  • UploadGenerationsPerBatch

  • UploadReadChangesPerBatch

  • UploadWriteChangesPerBatch

  • UseInprocLoader

  • Validate

  • ValidateInterval

For a Queue Reader Agent (agent_type=9), if defined in the profile, the following properties can be changed:

  • HistoryVerboseLevel

  • LoginTimeout

  • Output

  • OutputVerboseLevel

  • PollingInterval

  • QueryTimeout

  • ResolverState

  • SQLQueueMode

To see what parameters have been defined for a given profile, run sp_help_agent_profile and note the profile_name associated with the profile_id. With the appropriate profile_id, next run sp_help_agent_parameters using that profile_id to see the parameters associated with the profile. Parameters can be added to a profile by executing sp_add_agent_parameter.

[ @parameter_value = ] 'parameter_value' Is the new value of the parameter. parameter_value is nvarchar(255), with no default.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_change_agent_parameter is used in all types of replication.

Permissions

Only members of the sysadmin fixed server role can execute sp_change_agent_parameter.

See Also

Replication Agent Profiles
Replication Distribution Agent
Replication Log Reader Agent
Replication Merge Agent
Replication Queue Reader Agent
Replication Snapshot Agent
sp_add_agent_parameter (Transact-SQL)
sp_drop_agent_parameter (Transact-SQL)
sp_help_agent_parameter (Transact-SQL)
System Stored Procedures (Transact-SQL)