SQL Server Performance

Synchronization schedule for Transactional queued replication

Discussion in 'SQL Server 2005 Replication' started by Megan, Dec 15, 2008.

  1. Megan New Member

    I was able to setup transactional queued replication between 2 databases and everything seems to be working well. Only thing is - I have defined the synchronization to take place at a certain time each day, however the data is still synchronizing immediately. I only want it to synchronize once a day to minimize load during business hours. Any ideas why this is happening and how I can change it?
    Thank you very much.
  2. satya Moderator

    Welcome to the forums.
    Check the agent log for more information on why this is taking long or not working as expected.
    When was the lastime the agent was restarted?
    What is the edition of SQL Server you are using?
  3. Megan New Member

    Thank you for your prompt response.
    I'm using SQL Server 2005. I checked the agent log, see below and did some googling and figured it out and now its working. I read somewhere that the username must have access to the replication folder, so I modified the SQL Agent log in to use the same username as the replication and seems to have fixed it.
    Executed as user: NT AUTHORITYSYSTEM. Agent shutdown. For more information, see the SQL Server Agent job history for job 'DEVELOPER12-OMATest-OMATestPublication-NATIONAL51-6'. [SQLSTATE 01000] (Message 20557) Replication-Replication Distribution Subsystem: agent DEVELOPER12-OMATest-OMATestPublication-NATIONAL51-6 failed. Executed as user: NationalSQLService. The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed. [SQLSTATE 42000] (Error 14151). The step failed.
    Thanks again. :)
  4. satya Moderator

    Check why the agent is getting shutdown, the agent will run as the sql server agent's account and this needs to be a domain user and have read/write access to the repldata folder.
  5. Megan New Member

    Hi Satya,
    The sql server agent's log on was using the Local System account. I modified the log on to use a domain user account and gave it access to the repldata folder. I also deleted my publication and subscription, and started fresh from scratch and replication from Publisher to subscriber is working. The data is synchronizing at the set scheduled time.
    Thank you very much for your help.

Share This Page