I am quite new to working with SQL Server and I have been asked to replicate a database between 2 sites.
After looking at the options I decided that Transactional Replication was the best option so I proceeded to set it up.
Everything seemed to go okay, I setup my main server (SQL 7.0) as the Distributor and the Publisher, created my publication and I then configured my second server (SQL 2000) to use a Pull Subscription.
I then went back to the first server and generated the initial snapshot which took a while but seemed to run okay, I then synchronized using the Pull Subscription and that also worked fine.
Problem I am having now is the Log Reader Agent on the Publisher seems to be hogging CPU power and my cpu usage is permanently above 95% (if I stop SQl server then it falls to around 25% which is normal for this server).
It always shows a status of Running Delivering Replicated Transactions.
We are planning on replicating this database once a day so does the Log reader Agent need to be running all the time, I assumed it would as the way I understand it the Log Reader Agent monitors all transactions going into transaction log and puts all INSERT, UPDATE and DELETE statements into the Transaction database ready for replication.
If it does need to be running then does anyone know how I can stop it from hogging all of the CPU - my server is an IBM Netfinity x232, 2 1Ghz Xeon Processors and 2GB physical memory.
log reader agent will know the last place in the log it recorded, and should be able to pick up from there when restarted. in Agent Properties
set the desired schedule. (perhaps once an hour)
One thing to be carfeul of, is not to truncate your publication databases
logs before they have been delivered to repl. (see "sync with backup" option for distribution database)
Agent will report "Delivering Replicated Transactions" when doing a batch of over (i think) 10000 records...if under that it will report the exact number delivered. Your db must be pretty busy if that is the constant state!
If its is possible, is better to have replication (all agents) running continuously...
Also, take a look at your agent history and see if it encountering errors
and also make sure you are only replicating what you need as that size server should manage fine (we have the same with merge and transactional running for two OLTP sites, and our replication barely affects cpu. ours are all sql 2000 though, so maybe this has to do with your subscriber being diff version)
look in Books Online on optimizing transactional replication.
hope this helps