By: Tim Ford | Comments | Related: > Replication
Problem
An application analyst questioned me about replication problems. Specifically, the existing publication was no longer successfully being published via transactional replication. When reviewing the issue through the Replication Monitor, the error messaging was leaving me wanting more detail to find out why the replication process was failing. So my question is, is there a way to garner more information from the replication agents running via SQL Server Agent?
Solution
My very first MSSQLTips.com article was on SQL Agent Verbose Logging (see tip here.) So my immediate thought was sure, definitely, see that tip and let's go grab some coffee. Then I checked into the matter, and sure enough, there is no option available for configuring an output file as there is for a SQL Server Agent job step that executes T/SQL code as was referenced in that first tip.
Different steps had to be taken to get to a more-detailed level of logging and it would need to come from within the code that was being executed, not externally, from the job wrapper. Let's take a look at sample code from a Distribution Agent Job that does not have any detailed logging. This code is created by default upon creation of the publication, and is associated with the Run Agent step of that SQL Agent Job:
-Publisher [DEV01] -PublisherDB [SQLTips_TEST] -Distributor [DEV01] -DistributorSecurityMode 1 -Continuous -EncryptionLevel 1
The code specifies parameter values for the Publisher, the Distributor, the Security Mode of the Distributor, and the Encryption Level. These are just a few of the available parameters, but the ones populated by default nonetheless. I encourage you to review Microsoft SQL Server Books Online for a complete listing of parameters available to not only the Distribution Agent Job, but also the Log Reader Agent Job and Snapshot Agent Job that this tip applies to.
To enable verbose logging you must add two parameter values to the Run Agent step of the replication agent job for which you wish to review more detailed logging:
- -OutputVerboseLevel [0|1|2]
- -Output [Full Output Path and File Name]
The OutputVerboseLevel parameter accepts one of three integer values (0,1 or 2 as presented above).
- A value of 0 infers that there is no verbose logging on internal commands in the Agent, whereas
- a value of 1 will result in minimal logging and finally
- a value of 2 signifies the most-detailed logging you can expect from the associated Replication Agent Job.
The Output parameter is the full file path and file name for the associated log file you wish to generate and log to. If the file does not exist it will be created; an existing file will be appended to. The path of the file must be valid.
In my situation I altered the Distribution Agent as follows:
-Publisher [DEV01] -PublisherDB [SQLTips_TEST] -Distributor [DEV01] -DistributorSecurityMode 1 -Continuous -EncryptionLevel 1 -OutputVerboseLevel 2 -Output L:\Temp\Distribution_Agent.log
I've included code snippets from the generated log file below, bearing in mind that I've redacted data to protect my customers:
2009-01-21 20:45:44.273 Copyright (c) 2005 Microsoft Corporation
2009-01-21 20:45:44.289 Microsoft SQL Server Replication Agent: distrib
2009-01-21 20:45:44.289
2009-01-21 20:45:44.289 The timestamps prepended to the output lines are
expressed in terms of UTC time.
2009-01-21 20:45:44.289 User-specified agent parameter values:
-Subscriber APP17
-SubscriberDB SQLTips_TEST
-Publisher DEV01
-Distributor DEV01
-DistributorSecurityMode 1
-Publication SQLTips_TESTTransactional
-PublisherDB SQLTips_TEST
-Continuous
-EncryptionLevel 1
-Output L:\temp\distributor.log
-OutputVerboseLevel 2
-XJOBID 0x09599CBFBE271A4AA6343D208DC20ADE
-XJOBNAME DEV01-SQLTips_TEST-SQLTips_TESTTransactio-APP17-80
-XSTEPID 2
-XSUBSYSTEM Distribution
-XSERVER DEV01
-XCMDLINE 0
-XCancelEventHandle 0000000000000728
-XParentProcessHandle 0000000000000940
2009-01-21 20:45:44.304 Startup Delay: 9443 (msecs)
2009-01-21 20:45:53.752 Connecting to Distributor 'DEV01'
2009-01-21 20:45:53.752 Connecting to OLE DB Distributor at datasource: 'DEV01',
location: '', catalog: '', providerstring: '' using provider 'SQLNCLI'
2009-01-21 20:45:53.798 OLE DB Distributor: DEV01
DBMS: Microsoft SQL Server
Version: 09.00.4035
catalog name:
user name: guest
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2009-01-21 20:45:53.798 OLE DB Distributor 'DEV01': exec sp_helpdistpublisher
N'DEV01'
2009-01-21 20:45:53.814 OLE DB Distributor 'DEV01': select @@SERVERNAME
2009-01-21 20:45:53.814 OLE DB Distributor: DEV01
DBMS: Microsoft SQL Server
Version: 09.00.4035
catalog name: distribution
user name: DOMAIN\svc_Foo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2009-01-21 20:45:53.814 OLE DB Distributor 'DEV01': execute sp_server_info 18
2009-01-21 20:45:53.814 ANSI codepage: 1
OLE DB Distributor 'DEV01': select datasource, srvid from master..sysservers
where upper(srvname) = upper(N'DEV01')
2009-01-21 20:45:53.814 OLE DB Distributor 'DEV01': select datasource, srvid
from master..sysservers where upper(srvname) = upper(N'APP17')
2009-01-21 20:45:53.830 Subscriber security mode: 1, login name: .
2009-01-21 20:45:53.830 The concurrent snapshot for publication 'SQLTips_TESTTransactional'
is not available because it has not been fully generated or the Log Reader Agent
is not running to activate it. If generation of the concurrent snapshot was
interrupted, the Snapshot Agent
2009-01-21 20:46:53.809 The concurrent snapshot for publication 'SQLTips_TESTTransactional'
is not available because it has not been fully generated or the Log Reader Agent
is not running to activate it. If generation of the concurrent snapshot was
interrupted, the Snapshot Agent
2009-01-21 20:47:48.542 The process was successfully stopped.
2009-01-21 20:47:48.542 Disconnecting from OLE DB Subscriber 'APP17'
2009-01-21 20:47:48.542 Disconnecting from OLE DB Subscriber 'APP17'
2009-01-21 20:47:48.542 Disconnecting from OLE DB Distributor 'DEV01'
2009-01-21 20:52:10.629 Microsoft SQL Server Distribution Agent 9.00.4035.00
2009-01-21 20:52:10.629 Copyright (c) 2005 Microsoft Corporation
2009-01-21 20:52:10.629 Microsoft SQL Server Replication Agent: distrib
2009-01-21 20:52:10.644
2009-01-21 20:52:10.644 The timestamps prepended to the output lines are
expressed in terms of UTC time.
2009-01-21 20:52:10.644 User-specified agent parameter values:
-Subscriber APP17
-SubscriberDB SQLTips_TEST
-Publisher DEV01
-Distributor DEV01
-DistributorSecurityMode 1
-Publication SQLTips_TESTTransactional
-PublisherDB SQLTips_TEST
-Continuous
-EncryptionLevel 1
-Output L:\temp\distributor.log
-OutputVerboseLevel 2
-XJOBID 0x09599CBFBE271A4AA6343D208DC20ADE
-XJOBNAME DEV01-SQLTips_TEST-SQLTips_TESTTransactio-APP17-80
-XSTEPID 2
-XSUBSYSTEM Distribution
-XSERVER DEV01
-XCMDLINE 0
-XCancelEventHandle 0000000000000998
-XParentProcessHandle 0000000000000950
2009-01-21 20:52:10.660 Startup Delay: 4244 (msecs)
2009-01-21 20:52:14.910 Connecting to Distributor 'DEV01'
2009-01-21 20:52:14.910 Connecting to OLE DB Distributor at datasource: 'DEV01',
location: '', catalog: '', providerstring: '' using provider 'SQLNCLI'
2009-01-21 20:52:14.956 OLE DB Distributor: DEV01
DBMS: Microsoft SQL Server
Version: 09.00.4035
catalog name:
user name: guest
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2009-01-21 20:52:14.956 OLE DB Distributor 'DEV01': exec sp_helpdistpublisher
N'DEV01'
2009-01-21 20:52:14.972 OLE DB Distributor 'DEV01': select @@SERVERNAME
2009-01-21 20:52:14.972 OLE DB Distributor: DEV01
DBMS: Microsoft SQL Server
Version: 09.00.4035
catalog name: distribution
user name: DOMAIN\svc_Foo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2009-01-21 20:52:14.972 OLE DB Distributor 'DEV01': execute sp_server_info 18
2009-01-21 20:52:14.972 ANSI codepage: 1
OLE DB Distributor 'DEV01': select datasource, srvid from master..sysservers
where upper(srvname) = upper(N'DEV01')
2009-01-21 20:52:14.988 OLE DB Distributor 'DEV01': select datasource, srvid
from master..sysservers where upper(srvname) = upper(N'APP17')
2009-01-21 20:52:14.988 Subscriber security mode: 1, login name: .
2009-01-21 20:52:14.988 The concurrent snapshot for publication
'SQLTips_TESTTransactional' is not available because it has not been fully
generated or the Log Reader Agent is not running to activate it. If generation
of the concurrent snapshot was interrupted, the Snapshot Agent
It is important to note that once you determine the cause of your replication error(s) and correct the matter, be sure to remove the verbose logging parameters from your Replication Agent jobs. Otherwise performance can be degraded, and you will eventually run out of space because the logging does not have a cleanup behavior or rollover capability.
By turning on the verbose logging I was able to find my issue with replication.
Next Steps
- Review the SQL Server Agent Verbose Logging Tip.
- Consult Microsoft SQL Server Books Online for additional parameters available to further customize your replication instances.
- Additional Microsoft SQL Server Replication tips can be browsed here.
About the author
Tim Ford is a Senior Database Administrator with MindBody.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
