sp_disableagentoffload (Transact-SQL)

Disables remote push agent activation of the replication push agent that is identified by the @job_id parameter. This stored procedure is executed at the Publisher on the publication database.


Support for remote agent activation has been dropped for Distributors running Microsoft SQL Server 2005 and later.


sp_disableagentoffload [ @job_id = ] job_id
    [ , [ @offloadserver = ] 'remote_agent_server_name' ]
    [ , [ @agent_type = ] 'agent_type' ]


  • [ @job_id=] 'job_id'
    Specifies the SQL Server Agent job identifier of the replication agent to be disabled from remote activation. job_idis varbinary(16), with no default.
  • [ @offloadserver=] 'remote_agent_server_name'
    Specifies the network name of server to be used for remote agent activation. remote_agent_server_nameis sysname, with a default of NULL. If NULL, then the current offload_server in the MSDistribution_agents table is used.
  • [ @agent_type=] 'agent_type'
    Is the type of agent. agent_type is sysname, with a default of NULL, which specifies that the system determines if the agent type is distribution or merge. Valid values are distribution or merge, or NULL.

Return Code Values

0 (success) or 1 (failure)


sp_disableagentoffload is used to remove the ability to run the Distribution Agent or Merge Agent processing on another server.

Upon successful completion of sp_disableagentoffload, the –Offloadoffloadserver parameter is removed from the replication agent command line. Also, the offload_enabled field for the agent in MSdistribution_agents (Transact-SQL) is set to 0, and the offload-server field is updated with the new value specified in the 'remote_agent_server_name', if provided.


Only members of the sysadmin fixed server role or db_owner fixed database role or the subscription owner of the specified agent can execute sp_disableagentoffload.

See Also


sp_enableagentoffload (Transact-SQL)
System Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance