sp_help_rep_agent

Description

Displays static and dynamic information about a RepAgent thread.

Syntax

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

Parameters

dbname: The name of the database with the RepAgent for which you want information.

recovery: Displays recovery status information about the RepAgent.

config: Displays configuration information about the RepAgent.

process: Displays information about the RepAgent process.

scan: Displays log-scanning information about the RepAgent.

security: Displays current settings of the network-based security mechanism.

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'

Rep 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'

Rep Agent Process Status
dbname  spid   sleep status  retry count   last error
-----   ----   ------------  ----------    ----------   
pubs2   40     not sleeping  0             0

Example 3

Displays scanning information.

sp_help_rep_agent pubs2, 'scan'

Replication Agent Scan status
-----------------------------
dbname start marker end marker current marker log recs scanned oldest trans.
-------------------------------------------------------------------------
pubs2  (472675,13) (278622,0) (265736,16)          890       (472675,13)

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.

Table 5-2 describes the output for the sp_help_rep_agent 'recovery' system procedure.

**Table 5-2: 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.

Table 5-3 describes the output for the sp_help_rep_agent 'config' system procedure.

**Table 5-3: Column descriptions for sp_help_rep_agent 'config' output**
Column	Description
dbname	The name of the database with the data RepAgent is transferring to the Replication Server.
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. 4 (high priority) 5 (medium priority) 6 (low priority) The default value of priority is 5.
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.

Table 5-4 describes the output for the sp_help_rep_agent 'process' system procedure.

**Table 5-4: Column descriptions for sp_help_rep_agent 'process' output**
Column	Description
dbname	The name of the Replication Server to which RepAgent is transferring log transactions.
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.
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.

Table 5-5 describes the output for the sp_help_rep_agent 'scan' system procedure.

**Table 5-5: Column descriptions for sp_help_rep_agent 'scan'output**
Column	Description
dbname	The name of the Replication Server to which RepAgent is transferring log transactions.
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.

Table 5-6 describes output for the sp_help_rep_agent 'security' stored procedure.

**Table 5-6: Column descriptions for sp_help_rep_agent 'security' output**
Column	Description
dbname	The name of the Replication Server to which RepAgent is transferring log transactions.
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.”

Permissions

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