I’ve had numerous occasions in which I see something going wrong with replication, but rarely are the error messages clear and, to be honest, most of these messages simply specify something along the line of “enable verbose logging”.
This exact situation happened recently in which replication inexplicably decided that working was for suckers and it simply wasn’t going to bother… again only informing me that “verbose logging” was required.
Well, logging is exactly what I did and, although it’s something I use regularly, it was a topic that I was asked about recently by someone seeing that message for the first time and who had no idea how to go about implementing it.
So here’s how easy it is…
For this example I’ve simply knocked up a couple of databases called ReplTest and ReplTestReceive which I’ll use as my published and subscribed databases. These already have a table in each called testRepl…
I’ve also already set up the publication and subscribed to it:
So now we’re good to go.
Let’s find the scheduled job that’s been set up and look at the steps… there should be one called “Run Agent”…
Double click that step and look at the contents:
You can see that there’s a command in there which is a series of minus signs followed by keywords. These are basically the parameters which replication uses to function. In order to add logging we’re simply going to add the following string to the end of this command:
-outputverboselevel 2 -historyverboselevel 2 -output c:\myReplicationLog.txt
This tells SQL to log everything that’s happening and to write that to the specified file. Obviously if you don’t have access to the C drive, then pick a location that you’re happy with.
Press OK in order to save your changes and then run your job.
Once the job finishes running you will have a text file in the location you specified. Open that and you can see everything SQL Server did during that run.
As an example, I ran the following script:
insert into testRepl select 1, 'Homer', 'Simpson'
This is a snippet from the logged text file replication created:
Therefore you can see that the logging will even give you the data itself that it is dealing with which means that, in conjunction with logged errors etc., you can quickly and easily isolate any troubling transactions which are causing your replication to fail.
NB - The one caveat I will mention though, is that these logging files are cumulative and therefore once you have your log and your information, don’t forget to go back to your agent job and remove the logging parameters. Otherwise you could find that one day your disk fills up due to an incredibly large text file.