Post reply

Log Reader Agent

  • 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

  • March 13, 2007 at 10:14 am

    #694736

    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

  • March 13, 2007 at 12:16 pm

    #694780

    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

  • March 13, 2007 at 2:09 pm

    #694820

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

     

    Thanks,

    MCTS

  • March 14, 2007 at 7:02 am

    #694911

    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

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

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