Skip to content

sp-change-agent-parameter-transact-sql.md

Latest commit

 

History

History
185 lines (153 loc) · 6.01 KB

sp-change-agent-parameter-transact-sql.md

File metadata and controls

185 lines (153 loc) · 6.01 KB
title sp_change_agent_parameter (Transact-SQL)
description Changes a parameter of a replication agent profile stored in the MSagent_parameters system table.
author markingmyname
ms.author maghan
ms.reviewer randolphwest
ms.date 06/23/2025
ms.service sql
ms.subservice replication
ms.topic reference
f1_keywords
sp_change_agent_parameter_TSQL
sp_change_agent_parameter
helpviewer_keywords
sp_change_agent_parameter
dev_langs
TSQL

sp_change_agent_parameter (Transact-SQL)

[!INCLUDE SQL Server SQL MI]

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.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

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

Arguments

[ @profile_id = ] profile_id

The ID of the profile. @profile_id is int, with no default.

[ @parameter_name = ] N'parameter_name'

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.

If a parameter is supported for a given agent_type, but isn't 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 are 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 = ] N'parameter_value'

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.

Related content