How to Enable Logging of the Agent's Output
Occasionally, you might need to tweak the distribution agent so it logs the output to a text file. This can be accomplished by specifying output parameter as well as the full path where the agent output will be stored. You can also specify the OutputVerboseLevel parameter to define how much detail you want to have included in the file (1 being the least, and 3 being the most). For example, I can append the following to the distribution agent to log the output in the d:\agent1.txt file:
-output d:\agent1.txt -outputverboselevel 3
The full output is quite lengthy, so I won't include it here. However, the header looks similar to the following:
Microsoft SQL Server Distribution Agent 8.00.760 Copyright (c) 2000 Microsoft Corporation Microsoft SQL Server Replication Agent: full_name_of_distribution_job Startup Delay: 487 (msecs) Connecting to Subscriber 'subscriber_srvr' Repl Agent Status: 3 Connecting to Subscriber 'subscriber_srvr.subscriber_database' Server: subscriber_server DBMS: Microsoft SQL Server Version: 08.00.0760 user name: dbo API conformance: 2 SQL conformance: 1 transaction capable: 2 read only: N identifier quote char: " non_nullable_columns: 1 owner usage: 31 max table name len: 128 max column name len: 128 need long data len: Y max columns in table: 1024 max columns in index: 16 max char literal len: 524288 max statement len: 524288 max row size: 524288 ....
In addition the replication agent output includes every command ID, transaction ID, command text, and more. If you enable the agent output, be sure to recycle your files often; as you might imagine, logging every replicated transaction generates a huge amount of text. If you're not careful, you can easily run out of disk space.