SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Non-Continous Replicaton and The LogReader

By Andy Warren,

Last year I wrote an article about Non Continuous Replication, something that I began to use at work because the overhead of more than a 100 log readers at the time was loading the server pretty heavily. Basically when you set up a transactional publication regardless of whether you choose continuous or scheduled the log reader always runs. You can change that behavior by removing the "-continuous" from step 2 of the the logreader agent job, which looks something like this:

-Publisher [EG] -PublisherDB [Northwind] -Distributor [EG] -DistributorSecurityMode 1 -Continuous

The point in doing so is to save the approximate 4m of memory (SQL2K) that each logreader uses. Works pretty good, but there is a downside. Once you publish a database for transactional replication every transaction is inspected by the logreader and marked as complete once it is posted to the distribution database OR determined to be a transaction that doesn't affect the publication. There can be a great number of transactions that won't ever get distributed, like index rebuilds. If the logreader is running these get cleared almost instantly. Turn the logreader off and watch the log file grow! Not even a transaction log backup will reduce the file size, you HAVE to get the logreader to process the transactions before the log can shrink. As long as you run the log reader often enough to keep the log size manageable you're in good shape.

Ever look to see what exactly the logreader agent job does? It logs a "starting agent" message, then it runs the program logread.exe found under the COM folder (under SQL of course). If you set up a transactional publication, stop the log reader, you can see it in action by taking the code from step two and executing like this:

"c:\program files\microsoft sql server\80\com\logread.exe" -Publisher [EG] -PublisherDB [Northwind] -Distributor [EG] -DistributorSecurityMode 1 -Continuous

You'll then see it running..and running...and running. To stop it you have to enter a Control-C to get the application to terminate cleanly.

Now if you had to guess, what would happen if you ran it without the "-continuous" parameter?

It runs once, but to exit you still have to do the Control-C. You never see this if you run it from a job, SQL Agent handles it all for you including somehow getting the application to close. But what if we want to replicate maybe a 100 databases in non-continuous mode and we'd like to put together a nifty loop to just run the logread.exe once for each database? At the time I was working on this I couldn't figure out a way to get around the Control-C (other than sending keystrokes to the window!) so I ended up running the job that ran the log reader agent, leveraging SQL Agent's ability to stop the logread.exe.

An okay solution and one that has held up pretty well. Still it bugged me that I couldn't do it a little more cleanly, but a few months ago I came across a solution that I think will do the trick. There is a way to alter the logread.exe so that it does NOT require the Control-C! It uses a little known (to me anyway) ability of a Microsoft application called EditBin to alter the behavior. To change logread.exe to automatically exit, run this:

editbin.exe /SUBSYSTEM:CONSOLE "c:\program files\microsoft sql server\80\com\logread.exe"

And to change it back, run this:

editbin.exe /SUBSYSTEM:WINDOWS "c:\program files\microsoft sql server\80\com\logread.exe"

A couple things to remember. One is that you're changing the behavior of the logreader across all publications and all instances on the server. The other is that when you apply a service pack there is a chance the logread.exe will be replaced and it will then have the original behavior. The usual caveats and cautions apply!

I know this isn't your every day replication scenario. But having a bit deeper understanding of some of what goes on behind the scenes may help you one day. Wish I knew then what I know now! Thanks for taking the time to read this and if you have any comments or questions, please post in the attached discussion forum.

Total article views: 4918 | Views in the last 30 days: 2
Related Articles

The process could not execute 'sp_replcmds' on 'server_name'

LogReader agent not working


urgent help needed to solve logreader failure!!!!

logreader failed on trans replication with error that Another agent is running


LogReader agent failed

You may get an error on log reader agent failed with following error:   Cannot execute as the databa...


Non_Continuous Replication

Replicating continuously minimizes latency, but at a cost. If you're replicated a lot of databases, ...


killing the LogReader agent process??

I'm trying to kill the log reader process and restart the log reader agent. I continue to receive t...

sql server 7