SQL Server Performance Forum – Threads Archive
data missing in transactional replication
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.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.
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.
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.
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.
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.
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.
]]>