Scheduling the Log Reader Agent

Editor's Note: Share your SQL Server tips and tricks with other SQL Server Magazine readers. Email your ideas (400 words or fewer) to [email protected]. If we print your submission, you'll get $50.

If you have multiple replicated databases that you don't have to update in realtime, you typically schedule your Distribution Agent to run hourly or even once a day. This practice decreases the processor load on your SQL Server machine. You can further alleviate the processor load if you also schedule your Log Reader Agent instead of running it continuously, which is the default.

Set Log Reader Agent start times so that they don't overlap. SQL Server will execute one Log Reader Agent job at a time in a cycle that encompasses all the replicated databases. Thus, you change many jobs executing in parallel into a series of jobs that cycle through the databases.

Besides decreasing the processor load, another less obvious advantage of using scheduled Log Reader Agents is that now you can access the Log Reader Agent history and see, for example, the time of day when the maximum number of transactions occurred.

Listing 1 shows the code that will schedule all Log Reader Agents to run one after the other. The start time is arbitrary; for this example, I chose midnight plus the recurrence interval. The interval between jobs depends on the number of replicated databases and the cycle time, which you tailor so that the jobs won't overlap. If you have four replicated databases and you choose to schedule the Log Reader Agent to run at 15-minute intervals on each database, the stagger interval can be between 1 and 3 minutes. To include more replicated databases in the cycle, you must increase the total length of the cycle or decrease the stagger interval to accommodate all the jobs. In the example that Listing 1 shows, you could extend the number to 14 databases running the Log Reader Agent at 1-minute intervals.

Web Listing 1 shows the stored procedure in the master database that will let the code in Listing 1 schedule all Log Reader Agents on the server. (You can download this Web listing from the SQL Server Magazine Web site at http://www.sqlmag.com. Enter 24583 in the InstantDoc text box and click Download the code.) To reset the Log Reader Agents to the default (i.e., running continuously), run the stored procedure with no parameters besides the database name.

—Mircea Nita
[email protected]