Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Expand Minimize

sp_change_agent_parameter (Transact-SQL)

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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.

Topic link icon Transact-SQL Syntax Conventions

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

[ @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 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.

0 (success) or 1 (failure)

sp_change_agent_parameter is used in all types of replication.

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

Community Additions

ADD
Show:
© 2015 Microsoft