SQL Server Performance

data missing in transactional replication

Discussion in 'SQL Server 2005 Replication' started by masqazi, May 21, 2007.

  1. masqazi New Member

    recently i find out that some of the records are missing during the transactional replication.

    only thing that happen is that user stop the synchronization and then restart. otherwise the replication is running without any problem.

    what could be the reason and how to find the total missing info and how to rectify it.
  2. satya Moderator

    Run the profiler or server side trace to see why they are deleted, or check if they are not committed.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. MohammedU New Member

    Stop and start syncronisation should not cuase missing data...
    Uncomitted transactions will not be replicated only committed will replicate...
    what type of replication are you using...there may be some one/application deleted the data at the subscriber...

    You can run the sql trace on both servers to see what is happenning...
    You can't track what was happend unless you want to analyze tlog backups using third party tools...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  4. masqazi New Member

    that means i cant find out why this happen, so that i prepare for the future.

    this is very rare so i just cant put profiler to check it.

    Important: i figure out which data is missing, now how should i update the subscribier. these are new records in the table, so how can i insert them in subscriber.
  5. MohammedU New Member

    You can manually inserted the missing data to the susbcriber or you can reinitialize the snapshot...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  6. masqazi New Member

    Can i be due to stopping of log reader agent. suppose if my log reader agent is stopped due to any reason, what will happen to transactions that happen during that time.

  7. MohammedU New Member

    When the log reader agent stopped, no transactions will be replicated during that time...Once you starte all the pending transactions will be replicated...
    You will not loose any transactions unless you force them to clear the transactions before replicating them...

    By default log reader agent job will be scheduled to run when sql agent starts with 10 retry attempts I believe...

    If you don't want to intervene manually every time it fails for any reason...add another schedule to run the job every 10/30/60 minutes....


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page