question

LukeUhren-6505 avatar image
0 Votes"
LukeUhren-6505 asked Criszhan-msft answered

Transactional Replication Verbose Logs

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

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://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver15

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.