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.
