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. The information that sp_help_rep_agent displays for the database depends on the mode you configure for Rep Agent—single threaded, multipath replication, or multiple scanners.
recovery – Displays recovery status information about the RepAgent, including information on each replication path, the number of log records scanned by the scanner in each path, and the status of each scanner.
process – Displays information about the RepAgent threads.
config – Displays configuration information about the RepAgent.
scan – Displays log-scanning information about the RepAgent including the path associated with each scanner if you enable multiple scanners.
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 , sender and scanner spid numbers, and the path associated with each sender and scanner if you enable multiple paths and scanners.
all – Displays all the preceding information for the RepAgent enabled for the specified database.

Examples

Example 1 – Displays recovery information for single threaded RepAgent.

sp_help_rep_agent pdb, 'recovery'

You see:

Replication  Agent Recovery status

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

Example 2 – Displays recovery and path information for multithreaded RepAgent configured with the default path and one alternate replication path , and with multiple scanners set to false to enable only the single default scanner.

sp_help_rep_agent pdb, 'recovery'

You see :

Replication  Agent Recovery status

dbname   pathname   connect     connect   status      log records 
                    dataserver  database              scanned              
------   --------   ----------  --------  --------    -----------
pdb      default    sqlserver1   pdb      sleeping    6000
pdb      path1      sqlserver1   pdb      sleeping    5999

Example 3 – Displays recovery and path information for multithreaded RepAgent configured with two alternaet replication paths and the default path, and with multiple scanners set to true to enable multiple scanners.

sp_help_rep_agent pdb, 'recovery'

You see a row for each path if each scanner is performing recovery.

Replication  Agent Recovery status

dbname   pathname   connect     connect   status      log records 
                    dataserver  database              scanned              
------   --------   ----------  --------  --------    -----------
pdb      path1      sqlserver1   pdb      sleeping    5999

pdb      path2      sqlserver1   pdb      sleeping    6000

pdb      default    sqlserver1   pdb      sleeping    6000

Example 4 – Displays process information for single threaded RepAgent.

sp_help_rep_agent pdb2, 'process'

You see:

Replication Agent Process Status

dbname  spid   start_marker  end_marker  current_marker
-----   ----   ------------  ----------  --------------     
pdb2   12     (1240,0)      (1241,11)   (1241,11)   


sleep status  state     retry count   last error
------------  --------  ----------    ----------
not sleeping  sleeping  0             0

Example 5 – Displays process information for multithreaded RepAgent with two replication paths defined, and supported by the single default scanner.

sp_help_rep_agent pdb2, 'process'

You see:

Replication Agent Scanner Process Status

dbname  pathname  scanner_spid  start_marker  end_marker
------  --------  ------------  ------------  ----------
pdb2    n/a       12            (1240,0)      (1243,4)


current_marker  sleep_status  state
--------------  ------------  --------  
(1243,4)        end of log    sleeping


Replication Agent Sender Process Status

dbname  pathname  sender_spid  sleep_status  state 
------  --------  -----------  ------------  --------        
pdb2    default   22           empty queue   sleeping
pdb2    path1     14           empty queue   sleeping
pdb2    path2     15           empty queue   sleeping

retry_count  last_error  scanner_spid
-----------  ----------  ------------
0            0           12
0            0           12
0            0           12

Example 6 – Displays process information for multithreaded RepAgent with two replication paths, and supported by multiple scanners with one scanner thread for each path:

sp_help_rep_agent pdb2, 'process'

You see:

Replication Agent Coordinator Process Status

dbname  spid  sleep_status  state
------  ----  ------------  --------  
pdb2    13    sleeping      sleeping


Replication Agent Scanner Process Status

dbname  pathname  scanner_spid  start_marker  end_marker
------  --------  ------------  ------------  ----------
pdb2    default   25            (2055,0)      (2060,3)
pdb2    path1     12            (1240,0)      (1243,4)
pdb2    path2     11            (1109,0)      (1131,3)


current_marker  sleep_status  state
--------------  ------------  --------  
(2060,3)        end of log    sleeping
(1243,4)        end of log    sleeping
(1131,3)        end of log    sleeping


Replication Agent Sender Process Status

dbname  pathname  sender_spid  sleep_status  state 
------  --------  -----------  ------------  --------        
pdb2    default   22           empty queue   sleeping
pdb2    path1     14           empty queue   sleeping
pdb2    path2     15           empty queue   sleeping

retry_count  last_error  scanner_spid
-----------  ----------  ------------
0            0           25
0            0           12
0            0           11

Example 7 – Displays scanning information for the single default scanner with or without multiple replication paths.

sp_help_rep_agent pdb2, 'scan'

You see:

Replication Agent Scan status

dbname  pathname  scanner_spid  start_marker  end_marker   
------  --------  ------------  ------------  ----------   
pdb2    n/a       33            (74281,0)   (74281,0 )  

current_marker  log_recs_scanned   oldest_transaction
--------------  ----------------   -------------
( 74281,0)      8                  (0,0)

Note: There is no path name since replication paths are associated with the senders and not the scanner.

Example 8 – Displays scanning information for multithreaded RepAgent with two replication paths and the default path, and with multiple scanners.

sp_help_rep_agent pdb2, 'scan'

You see:

Replication Agent Scan status

dbname  pathname  scanner_spid  start_marker  end_marker   
------  --------  ------------  ------------  ----------   
pdb2    default   32            (21055,0)     (21060,3)    
pdb2    path1     33            (74281,0)     (74281,7)
pdb2    path2     34            (74281,0)     (74281,7)           

current_marker  log_recs_scanned   oldest_transaction
--------------  ----------------   -------------
(21060,3)       32                 (-1,0)
(74281,7)       32                 (0,0)
(74281,7)       32                 (0,0)

Example 9 – Display sender thread information for single threaded RepAgent.

sp_help_rep_agent pdb1, 'send'

You see:

Replication Agent Send Status

dbname  pathname  sender_spid   total_send_buffers   send_buffers_used  
-----   --------  -----------   ------------------   -----------------   
pdb1    n/a       12            0                    0


scanner_spid
------------
12

Note: For single threaded RepAgent with only the default scanner, the sender and scanner spids are the same.

Example 10 – Display sender thread information for multithreaded RepAgent with no replication paths defined and configured with either the default single scanner or multiple scanners.
```
sp_help_rep_agent pdb2, 'send'
```
You see only the default path:
```
Replication Agent Send Status

dbname  pathname  sender_spid   total_send_buffers   send_buffers_used
-----   --------  -----------   ------------------   -----------------   
pdb2    default   14            50                   0


scanner_spid
------------
12
```

Example 11 – Display sender thread information for multithreaded RepAgent with two defined alternate replication paths and the default path, and with multiple scanners.

sp_help_rep_agent pdb2, 'send'

You see:

Replication Agent Send Status

dbname  pathname  sender_spid   total_send_buffers   send_buffers_used
-----   --------  -----------   ------------------   -----------------   
pdb2    default   22            50                   0              
pdb2    path1     14            50                   0
pdb2    path2     21            50                   0

scanner_spid
------------
15
12
23

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 sp_help_rep_agent'recovery.

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.
`pathname`	The name of the replication path associated with each sender or scanner process if you configure multiple replication paths and scanners.
`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 or scanner 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. sleeping – RepAgent is in sleep mode and waiting for new records to scan and send. unknown – none of the above.
`log records scanned`	The number of database log records scanned by RepAgent scanner tasks.
`rs servername`	For single threaded RepAgent, the name of the Replication Server to which the RepAgent is transferring information. Use this option to override the sysattributes setting. `rs servername` does not display if you configure multiple scanners since there may be multiple destination Replication Servers from the primary database .
`rs username`	For single threaded RepAgent, the login name RepAgent uses to log in to the Replication Server. Use this option to override the sysattributes setting. `rs username` does not display if you configure multiple scanners since there may be multiple destination Replication Servers from the primary database.

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 for which you are querying configuration information.
`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.”
`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.
`multipath_distribution_model`	Specifies the replication distribution model for RepAgent where: object – sets the model to distribution by object binding which is the default connection – sets the model to distribution by connection filter – sets the model to distribution by column filter The default is object.
`multiple_scanners`	Enables or disables multiple RepAgent scanner threads. Set to true for RepAgent to generate multiple scanner threads with a scanner thread dedicated to each path in a multipath replication environment. Default is false where there is only a single scanner thread shared by all replication paths.

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 forwhich you are querying process information.
`pathname`	The name of the replication path associated with each sender or scanner process if you configure multiple replication paths and scanners.
`spid`	The system process ID of a process in the dataserver. For Single threaded RepAgent – spid identifies the RepAgent process that performs both the sender and scanner tasks. Multithreaded RepAgent – spid identifies the coordinator task if you enable multiple scanners.
`scanner_spid`	The system process ID of each scanner process in the dataserver.
`sender_spid`	The system process ID of each sender process in the dataserver .
`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.
`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. sleeping – RepAgent task is suspended and waiting for activity. empty queue – RepAgent sender task does not have any transactions in the queue to process and is waiting for activity. not sleeping – none of the above. RepAgent is active.
`state`	State values for a coordinator, scanner, or sender task: "Sleeping" – RepAgent task is suspended and waiting for activity. "Awake" – RepAgent task is active.
`retry count`	The number of times RepAgent has unsuccessfully attempted to connect to Replication Server since the last successful connection.
`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 for which you are querying sender thread information.
`pathname`	The name of the replication path associated with each sender or scanner process if you configure multiple replication paths and scanners.
`sender_spid`	The system process ID of each sender process in the dataserver.
`scanner_spid`	The system process ID of each scanner process in the dataserver.
`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 for which you are querying scanner thread information.
`pathname`	The name of the replication path associated with each sender or scanner process if you configure multiple replication paths and scanners.
`scanner_spid`	The system process ID of each scanner process in the dataserver.
`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 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 for which you are querying security information..
`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.