- Agent Security
- Agent Profiles
- Agent Properties
- Stored Procedures for Examining Replication Agents
- Summary
Stored Procedures for Examining Replication Agents
SQL Server 2005 includes several system-stored procedures that can help you examine agent profiles and parameters programmatically. Although with typical implementation you’ll probably examine agent profiles through SSMS, knowledge of these stored procedures can become handy if you’re managing a large number of servers and want to automate some administrative tasks. All stored procedures discussed in this section need to be executed against the distribution database.
Sp_help_agent_profile—returns the profile identifiers for a given type of replication agent (snapshot, distribution, log reader, merge or queue reader). For example, the following execution returns all distribution agent profiles defined on the current instance:
Results:
Profile ID |
Profile Name |
Description |
def_profile |
4 |
Default agent profile |
Null. |
1 |
5 |
Verbose history agent profile. |
Agent profile for detailed history logging. |
0 |
10 |
Windows Synchronization Manager profile |
Profile used by the Windows Synchronization Manager. |
0 |
14 |
Continue on data consistency errors |
Agent profile for skipping data consistency errors. It can be used only by SQL Server Subscribers. |
0 |
16 |
Distribution Profile for OLEDB streaming |
Distribution agent profile enabled for the processing LOB data using OLEDB streaming. |
0 |
17 |
User profile |
Null. |
0 |
Sp_help_agent_parameter—gets parameters for a specified profile; you can use the identifier retrieved by the sp_help_agent_profile procedure as the required parameter for this routine. For example, I could execute the following statement to get the parameters for the Windows Synchronization Manager profile:
Results:
Profile ID |
Parameter Name |
Value |
10 |
BcpBatchSize |
2147473647 |
10 |
CommitBatchSize |
100 |
10 |
CommitBatchThreshold |
1000 |
10 |
HistoryVerboseLevel |
1 |
10 |
KeepAliveMessageInterval |
300 |
10 |
LoginTimeout |
15 |
10 |
MaxBcpThreads |
1 |
10 |
MaxDeliveredTransactions |
0 |
10 |
PollingInterval |
5 |
10 |
QueryTimeout |
1800 |
10 |
SkipErrors |
|
10 |
TransactionsPerHistory |
100 |
Later, if I no longer need to ignore primary key violation errors I can remove this parameter with the following command: