Non-Continous Replicaton and The LogReader


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


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.


5 (1)




5 (1)