Tag Archives: Replication Jobs

Replication Logging

20 Nov

At SQLPASS 2015 I attended a presentation by Kendal Van Dyke, in which he told us that it was possible to log the actions of Replication to an output file.

This was news to me, so I just had to have a closer look.

The Setup

This will use the same Publication that I created in my earlier articles (Subscription and Distributor), which has been running on my test instances ever since.

The Commands

Replication is controlled via Agent Jobs and this is where the output commands are used. Every job involved in Replication (Log Reader, Queue Reader and Distributor) can make use of them.

-Output [Filename] is the name of the text file that the output will be written to. If the file does not exist then it will be created, otherwise it will be appended to.

-OutputVerboseLevel [0|1|2] is the level of output required, with 0 being the lowest and 2 the highest.


Implementing this is quite easy – located the Agent jobs for the Publication of interest and add the commands to end of the job’s command line.

The appropriate Log Reader Agent and Queue Reader Agent can be found via the Replication Monitor. Open Replication Monitor, from any of the Publisher, Distributor or Subscriber servers and locate the ‘Agents’ tab from the appropriate Publication. From the ‘Agent types’ drop-down select either ‘Log Reader Agent’ or ‘Queue Reader Agent’, then right-click on the entry and select ‘Properties’.


This will show the same control that selecting the job under SSMS ‘SQL Server Agent/Jobs’ and ‘Properties’ of the appropriate Job would have shown, but removes any doubt you have located the correct Log Reader Agent.

Of course, if you know the job involved then you can just locate each from the SSMS ‘SQL Server Agent/Jobs’ list on the appropriate servers and update them directly. In my Pull Subscription I have to locate the Distributor Job myself within SSMS – it doesn’t appear as an option via Replication Monitor.

Once the parameters have been added the jobs need to be stopped and started again, so the new command-line can be read.

The Output

With OutputVerboseLevel set to 0 only basic details are written to the text files – various settings of the publication, server names, job names and suchlike. The Distributor produces these details every time it runs, so even this can mean quite a large document given enough time. This is useful for getting error messages with basic history details.

OutputVerboseLevel of 1 also shows statistics, in addition to connection details and other information. This of course increase the output from any job using this setting.

OutputVerboseLevel of 2 is the most detailed level of output. This shows the stored procedure calls, batch details, connections, disconnections and so on. It is very useful for a short period of time but quickly fills drive space. Use this when you need a great deal of detail for a short period of time.

Replication – Monitoring via the Jobs

5 Nov

In the previous article I covered how to use the Replication Monitor to see what is happening with a Publication and to find out what the errors are.
However, in some circumstances Replication Monitor may not show the entire picture with regard to the health of a Publication.
Information can also be gleaned from the jobs that actually perform the tasks that make the Publication work.

In this example I have removed the login of the Subscriber from the Distributor with a simple DROP LOGIN command.

USE [master]



Now, on the Publisher I’ll add one row to the table that is part of the Publication:

USE [PublisherDB]

INSERT INTO [ReplTest].[PublicationTable]
           ,'23 Jul 1970'

With this Publication the data should be sent at about 30 seconds past each minute. Open the replication Monitor and look at the history of the Distribution:


However, this has been sitting here for several minutes now and the history has not moved – stuck at the last transaction that completed at 13:12:29. There is also no error message displayed, so this screen indicates that there must be a problem (because I know it should run every minute) but gives no details.

The tab for ‘Undistributed Commands’ shows that there is one transaction waiting to get to the Subscriber:


And firing a tracer token never gets to the Subscriber:


From the Tracer Token we can see that the Publisher to Distributor connection must be working and there is no response between the Distributor and Subscriber.

This Publication is a Pull Subscription, so on the Subscriber there is a job that pulls the data from the Distributor. So, look at the job history on the Subscriber. In this case there is only one Job so it is easy to select, but the name should have something within it that shows which Publication it is part of. Within the history for that job there is a long list of failures – one every minute oddly enough:


Expand the ‘tree’ from the red cross and the latest step just tells you there’s a problem and to look either in the previous job step or replication Monitor. We know that Replication Monitor has told us all it is going to, so look at the previous step of this job:


So now we know that the problem is that the job cannot connect to the Distributor. In this case recreating the LOGIN on the Distributor for this job will correct the issue:

USE [master]



And we can see that the Replication monitor shows two transactions successfully processed (because the Tracer Token is a transaction too):


The Tracer token finally completed its journey:


And the Job History looks fine too: