sp_help_rep

Displays static and dynamic information about a RepAgent thread.

Syntax

sp_help_rep_agent [dbname[, 'recovery' | 'process' | 'config' | 'scan' | 'security' | 'send' |'all']]

Parameters

dbname – The name of the database with the RepAgent for which you want information.
recovery – Displays recovery status information about the RepAgent.
process – Displays information about the RepAgent process for both single and multiple replication paths..
config – Displays configuration information about the RepAgent for both single and multiple replication paths.
scan – Displays log-scanning information about the RepAgent.
security – Displays current settings of the network-based security mechanism.
send – Displays information about the number of send buffers that you have allocated to RepAgent.
all – Displays all the preceding information for the RepAgent connected to the specified database.

Examples

Example 1 – Displays recovery information.

sp_help_rep_agent pubs2, 'recovery'

You see:

Replication  Agent Recovery Status

dbname   connect     connect   status    rs servername  rs username 
         dataserver  database              
------   ----------  --------  ------    -------------  -----------
pubs2    sqlserver1   pubs2    scanning  repsvr1        repusr1

Example 2 – Displays process information.

sp_help_rep_agent pubs2, 'process'

You see:

Replication Agent Process Status

dbname  spid   sleep status  retry count   last error
-----   ----   ------------  ----------    ----------   
pubs2   40     not sleeping  0             0

Example 3 –

Displays process information for multithreaded RepAgent

sp_help_rep_agent pubs2, 'process'

You see:

Replication Agent Process Status

dbname  spid   sleep status   state     retry count   last error
-----   ----   ------------   --------  ----------    ----------   
pubs2   12     not sleeping   sleeping  0             0


Replication Agent (sender) Process status

dbname  spid   sleep status   state     retry count   last error
-----   ----   -------------  -----     ----------    ----------   
pubs2   13     connect retry  sleeping  3             0
pubs2   14     connect retry  sleeping  3             0

Example 4 –

Display information on send buffers produced by scanner task and send buffers sent to Replication Server by sender task. The information is displayed only when the multithread rep agent parameter of sp_config_rep_agentis enabled.

sp_help_rep_agent pubs2, 'send'

You see:

Replication Agent Send Status

dbname  sender_spid   total_send_buffers   send_buffers_used
-----   -----------   ------------------   -----------------   
pubs2   13            40                   0

Example 5 – Displays scanning information.

sp_help_rep_agent pubs2, 'scan'

You see:

Replication Agent Scan status

dbname  start_marker  end_marker  current_marker 
------  ------------  ----------  -------------- 
pubs2   (472675,13)   (278622,0)  (265736,16)

log_recs_scanned   oldest_trans.
----------------   -------------
0                  (-1,0)

Example 6 –

sp_help_rep_agent pubs2, 'send'

You see:

Replication Agent Send Status

dbname  sender_spid   total_send_buffers   send_buffers_used
-----   -----------   ------------------   -----------------   
pubs2   13            40                   0

Usage

Use sp_help_rep_agent with RepAgent-enabled databases.
If you execute sp_help_rep_agent without parameters, Adaptive Server displays information about all databases for which RepAgent is enabled.

Column descriptions for sp_help_rep_agent 'recovery' output describes the output for the sp_help_rep_agent'recovery'system procedure.

Column Descriptions for sp_help_rep_agent 'recovery' Output
Column	Description
`dbname`	The name of the database containing archived logs whose data is transferred to the Replication Server during recovery.
`connect dataserver`	The name of the original data server with the database whose transaction logs were transferred to Replication Server in normal mode. This information is included in the LTL connect source command delivered to Replication Server.
`connect database`	The name of the original database whose transaction logs were transferred to Replication Server in normal mode. This information is included in the LTL connect source command delivered to Replication Server.
`status`	Indicates RepAgent activity. Status values are: “not running” – RepAgent is not running. “not active” – RepAgent is not in recovery mode. “initial” – RepAgent is initializing in recovery mode. “end of log” – RepAgent is in recovery mode and has reached the end of the transaction log. “unknown” – none of the above.
`rs servername`	The name of the Replication Server to which the RepAgent is transferring information. Use this option to override the sysattributes setting.
`rs username`	The login name RepAgent uses to log in to the Replication Server. Use this option to override the sysattributes setting.

Column descriptions for sp_help_rep_agent 'config' output describes the output for the sp_help_rep_agent 'config' system procedure.

Column Descriptions for sp_help_rep_agent 'config' Output
Column	Description
`dbname`	The name of the database containing archived logs whose data is transferred to the Replication Server during recovery.
`auto start`	Contains “true” if the RepAgent starts automatically during server start-up. Otherwise, contains “false.”
`rs servername`	The name of the Replication Server to which RepAgent is transferring log transactions.
`rs username`	The login name the RepAgent thread uses to log in to the Replication Server. The login name must have been granted connect source permission in the Replication Server.
`scan batch size`	The maximum number of log records sent to Replication Server in each batch. The default is 1000.
`scan timeout`	The number of seconds that RepAgent sleeps when it has scanned and processed all records in the transaction log and Replication Server has not yet acknowledged previously sent records by sending a secondary truncation point. The default is 15 seconds.
`retry timeout`	The number of seconds RepAgent sleeps before attempting to reconnect to Replication Server after a retryable error or when Replication Server is down. The default is 60 seconds.
`skip ltl errors`	Contains “true” if RepAgent ignores errors in LTL commands. Contains “false” if RepAgent shuts down when these errors occur. skip ltl errors is normally set to “true” in recovery mode. The default is “false.”
`batch ltl`	Contains “true” if RepAgent batches LTL commands and sends them to Replication Server. Contains “false” if LTL commands are sent to Replication Server as soon as they are formatted. The default is “false.”
`send warm standby xacts`	Contains “true” if RepAgent submits schema, system xacts, and all updates, including updates made by the maintenance user, to the Replication Server for application to the standby database in a warm standby application. Contains “false” if RepAgent is not submitting updates to the standby database. The default is “false.”
`connect dataserver`	The name of the data server RepAgent connects to Replication Server as when running in recovery mode. If RepAgent is not running in recovery mode, contains the name of the data server of the `dbname` database.
`connect database`	The name of the database RepAgent connects to Replication Server as when running in recovery mode. If RepAgent is not running in recovery mode, contains the `dbname` database name.
`send maint commands to replicate`	Contains “true” if RepAgent sends records from the maintenance user to replicate databases. Contains “false” if RepAgent does not send records form the maintenance user to replicate databases. The default is “false.”
`ha failover`	Specifies whether, when Sybase Failover has been installed, RepAgent starts automatically after server failover. The default is “true.”
`skip unsupported features`	Instructs RepAgent to skip log records for Adaptive Server features unsupported by the Replication Server. This option is normally used if Replication Server is an earlier version than Adaptive Server. The default is “false.”
`short ltl keywords`	Specifies whether RepAgent sends an abbreviated form of LTL to Replication Server, requiring less space and reducing the amount of data sent. The default value is “false.”
`send buffer size`	Controls the size of the send buffer that RepAgent uses to communicate with Replication Server. Increasing the size of the send buffer reduces the number of times RepAgent communicates with Replication Server, but increases the amount of memory used. Values are “2K,” “4K,” “8K,” and “16K.” The default value is “2K.”
`priority`	Sets relative priority values for individual RepAgents. The value of priority ranges from 0 to 7, where a value of 0 indicates highest priority. The default value is 5. Note: Sybase recommends that you do not set the value of priority to 0.
`send structured oqids`	Specifies whether RepAgent sends origin queue IDs (OQIDs) as structured tokens, which saves space in the LTL and thus improves throughput, or as binary strings. The default value is “false.”
`data limits filter mode`	Specifies how RepAgent handles log records containing new, wider columns and parameters, or larger column and parameter counts, before attempting to send them to Replication Server. off – RepAgent allows all log records to pass through. stop – RepAgent shuts down if it encounters log records containing wide data. skip – RepAgent skips log records containing wide data and posts a message to the error log. The default value of data_limits_filter_mode depends on the Replication Server version number. For Replication Server versions 12.1 and earlier, the default value is “stop.” For Replication Server versions 12.5 and later, the default value is “off.”
`schema cache growth factor`	Controls the duration of time table or stored procedure schema can reside in the RepAgent schema cache before expiring. Larger values mean a longer duration and require more memory. Range is 1 to 10. The default is 1.
`startup delay`	The number of seconds that the RepAgent start-up is delayed. The default is 0.
`cluster instance name`	The name of the cluster instance where the RepAgent is started. The default value is 'coordinator'.
`bind to engine`	The engine number where RepAgent is specified to execute on. Range is -1 to (max online engines - 1), where max online engines is an Adaptive Server configuration parameter. The default value is -1, which means RepAgent can execute on any engine.
`ltl batch size`	The maximum size, in bytes, of LTL data that RepAgent can send to the Replication Server for a given batch. The minimum and default value is 16,384 bytes. The maximum value is 2,147,483,647 bytes.
`multithread_rep_agent`	Specifies whether multithreaded RepAgent is enabled. Multithreaded RepAgent uses separate threads for the RepAgent scanner and sender activities, and is a prerequisite for building multiple primary replication paths. The default value is false.
`number_of_send_buffers`	The maximum number of send buffers that the scanner and sender tasks of multithreaded RepAgent can use. Range of valid values: 1 to the value of MAXINT which is 2,147,483,647 buffers. The default is 50 buffers.

Column descriptions for sp_help_rep_agent 'process' output describes the output for the sp_help_rep_agent 'process' system procedure.

Column Descriptions for sp_help_rep_agent 'process' Output
Column	Description
`dbname`	The name of the database containing archived logs whose data is transferred to the Replication Server during recovery.
`sleep status`	Sleep status values are: “waiting for rewrite” – RepAgent is waiting for a two-phase commit transaction to commit. “end of log” – RepAgent is at the end of the log, waiting for it to be extended. “connect retry” – RepAgent is waiting before attempting a connection to Replication Server. “not sleeping” – none of the above. RepAgent is active.
`state`	State values are: "Sleeping" – RepAgent sender task is suspended and waiting for activity. "Awake" – RepAgent sender task is active.
`retry count`	The number of times RepAgent has unsuccessfully attempted to connect to Replication Server since the last successful connection.
spid	The PID in the dataserver.
`last error`	The error number of the last Replication Server or connection error.

Column descriptions for sp_help_rep_agent 'send' output describes the output for the sp_help_rep_agent 'send' system procedure.

Column Descriptions for sp_help_rep_agent 'send' Output
Column	Description
`dbname`	The name of the database containing archived logs whose data is transferred to the Replication Server during recovery.
`sender_spid`	The PID of the RepAgent sender task.
`total_send_buffers`	The number of send buffers allocated to each sender task.
send_buffers_used	The number of send buffers used by the sender task.

Column descriptions for sp_help_rep_agent 'scan'output describes the output for the sp_help_rep_agent 'scan' system procedure.

Column Descriptions for sp_help_rep_agent 'scan' Output
Column	Description
`dbname`	The name of the database containing archived logs whose data is transferred to the Replication Server during recovery.
`start marker`	Identifies the first record scanned in current batch.
`end marker`	Identifies the last record to be scanned in current batch.
`current marker`	Identifies the record currently being scanned.
`log recs scanned`	The number of log records RepAgent has scanned in the current batch.
`oldest transaction`	Identifies the oldest transaction in the batch currently being scanned.

Column descriptions for sp_help_rep_agent 'security' output describes output for the sp_help_rep_agent 'security' stored procedure.

Column Descriptions for sp_help_rep_agent 'security' Output
Column	Description
`dbname`	The name of the database containing archived logs whose data is transferred to the Replication Server during recovery.
`security mechanism`	The name of the enabled security mechanism.
`unified login`	Specifies whether RepAgent seeks to connect to Replication Server with a credential (“true”) or a password (“false”). The default is “false.”
`mutual authentication`	Specifies whether RepAgent uses mutual authentication checks when connection to Replication Server. The default is “false.”
`msg confidentiality`	Specifies whether RepAgent uses message encryption on all data sent to Replication Server. The default is “false.”
`msg integrity`	Specifies whether RepAgent uses message integrity checks on all data exchanged with Replication Server. The default is “false.”
`msg replay detection`	Specifies whether RepAgent checks to detect whether data has been captured and replayed by an intruder. The default is “false.”
`msg origin check`	Specifies whether RepAgent verifies the source of data sent from Replication Server. The default is “false.”
`msg out-of-sequence`	Specifies whether RepAgent verifies that messages received from Replication Server are received in the order sent. The default is “false.”
`net password encryption`	Indicates whether or not the connection to a Replication Server is initiated with a client-side password encryption handshake. The default is “true”.

Permissions

sp_help_rep_agent requires “sa” or “dbo” permission or replication_role.