Share via

Transactional Replication Verbose Logs

Luke Uhren 191 Reputation points
2021-08-09T17:03:32.897+00:00

Is there a place that will show more verbose logs on what exactly is being replicated in a transactional replication publisher and subscriber setup?

Example, I have a SQL on a Azure VM replicating to an Azure SQL database. Where the Azure VM running SQL is the publisher and the Azure SQL database is the subscriber.

We added a ton of records in on the weekend and it mentioned in the replication monitor Log Reader Agent logs, that "Approximately 38000000 log records have been scanned of which were marked for replication. Viewing status of the replication just says "Delivering replicated transactions" and I see the database slowly growing in Azure SQL. That is all the logs are showing me and I am seeing if there is more?

I am wondering if there is a location or query to see what it is replicating exactly at this point and what exactly it is doing? I don't see any location other than replication monitor or I am just missing as to where it would be for what it's doing if I wanted to see.

Any help is appreciated

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,364 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Cris Zhan-MSFT 6,616 Reputation points
    2021-08-10T02:31:43.6+00:00

    Hi,

    Transactional replication uses Log Reader Agent and Distribution Agent to deliver data changes and schema modifications made on the publisher to the subscribers in almost real time. All data changes will be transaction-based, in the order in which they occur on the publisher, and then applied to the subscriber.

    The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database, which acts as a reliable store-and-forward queue. The Distribution Agent copies the initial snapshot files from the snapshot folder and the transactions held in the distribution database tables to Subscribers.
    https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver15

    View Replicated Commands and Information in Distribution Database.
    https://learn.microsoft.com/en-us/sql/relational-databases/replication/monitor/view-replicated-commands-and-information-in-distribution-database?view=sql-server-ver15

    0 comments