sp_change_agent_parameter (Transact-SQL)

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

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.

Reference

Other Resources

Help and Information