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.
Implementation
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.
Notes on SQL 