Log Reader Agent

Question

Log Reader Agent

mengus

Ten Centuries

Points: 1187
More actions
March 9, 2007 at 8:09 am

#123145

What type of query can I use to see what is the "Read Batch Size" of the Log Reader Agent used for Transactional Replication. By default, from my readings, Batch Size is either default as 500 or 1000 Transactions/Commits, but this value can be set for individual implementations. Batch Size can be used for both the Log Reader Agent and the Distribution Agent in Transactional Replication.
Is there any queries out there can I can use to check what is currently this value. Any help will be greatly appreciated.
Thanks,
SQL Server DBA
MCTS
March 12, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mengus Ten Centuries Points: 1187 More actions · Answer 1

Yes,

Any ideas would be greatly appreciated. The current configuration setting is that the Replicated Database and the Distribution Database are on the same server. The publisher is remote from these two. Right now, the batch size for the Distrubition Agent is 500. However, I would like to know if there is either a T/SQL query or if there is a parameter to LogRead.exe to check the batch size for the Log Reader Agent, without propagating the changes to agent. LogRead.exe is a command that comes with MS SQL Server, and can be found on the server that runs the SQL Server instance. Here is the Microsoft link, http://msdn2.microsoft.com/en-us/library/aa214008(SQL.80).aspx# .

Thanks,

MCTS

noeld SSC Guru Points: 96590 More actions · Answer 2

You need the help of Two stored procedures:

use distribution

sp_help_agent_profile @agent_type = 2 --LogReader=2

go

sp_help_agent_parameter @profile_id = 3

go

On the above example I used sp_help_agent_profile to determine what is the default (currently running) profile. Then I used sp_help_agent_parameter and passed the profile_id found on the first proc.

Note that this changes are recorded in the database but are picked up by the agents only after they are re-started

Cheers,

* Noel

mengus Ten Centuries Points: 1187 More actions · Answer 3

Thanks Noel for kind reply. I will try this out when the server is backed online.

Thanks,

MCTS

mengus Ten Centuries Points: 1187 More actions · Answer 4

The command works, I am able to find what I am seeking. Thank you for the heads up info on the system procedures. Plus, having the server back online also helped. I will test out some configuration to tune what I have.

Thanks,

MCTS