SQL Server Performance


Discussion in 'Performance Tuning for SQL Server Replication' started by NewDBA, May 22, 2003.

  1. NewDBA Member

    HI !

    I have VPN connected servers and i responsible for replicating data
    between them.(Transactional replication)
    Look like my destribution cleanup process is not working correctly.
    MSrepl_commands table grows and from time to time i have remove replication and recreate
    it again just to delete those milions commands in that table.

    My log reder profile is:
    HistoryVerboseLevel- 1
    loginTimeout - 30
    polingInterval - 10
    Querytimeout -500
    ReadBatchSize -500

    Is anything else i should consider to change to improve performance

    Thank you
  2. satya Moderator

    Any information from SQL Error log?

    Satya SKJ
  3. NewDBA Member

    Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..

    Error: 15457, Severity: 0, State: 1

    Maybe it somehow related to my drop and recreate replication.

    Because i keep droping replication in order to resolve my problem

    Can i manually delete commands from this table?
    Data is being replication fine
  4. satya Moderator

    Error 15457 are informational messages and not really error messages, if you change server properties then you will see this message. When using SP_CONFIGURE statement the message will be issued to run RECONFIGURE, refer to BOL for more information.

    Try to refresh the LOG READER Agent to affect the changes.

    Satya SKJ
  5. NewDBA Member

    I sure stoped and run log reader agent after changing it's properties
    I also tried to play with retention period .
    Initially it was set to 0.
    So all distributed transactions was suppose to be deleted imediately
    when distributon clean up agent run.
    I change it to 1 with hope for some refreshment or what
    it didn't help .
    I am so frusterated cannor find any information
  6. NewDBA Member

    I got it :
    Max retention period ! i set it to 10 hours and ta-da
    msrepl_commands is being cleaned by distribution clean up job
    I thing the explanation on msdn not very clear about this agent
  7. satya Moderator

    True can only be avail with experience and books.

    Satya SKJ

Share This Page