Transaction Logs: How much log data generated (hourly or daily)?
Lots of questions in there.
First, are we talking about transactional replication? I know you said that, but we need to be certain that you aren't talking about Always On Availability Groups, log shipping or some other solution to get a &quot;copy&quot; of your database.
Answered: yes, transaction replication - I will refer to it as just &quot;replication&quot;.
No, replication doesn't copy VLFs. I think it extremely difficult to get anything usable from the transaction log.
First of all, consider the replication architecture:
An Agent job, the Log Reader, reads the transaction log, generates DML statements from this and stored them in the distribution database. So first question is whether Log Reader is local or not.
Then the next Agent job, the Distributor reads these DML commands in the distribution database and applies them to each subscriber. And you probably realize, there is not a 1:1 mapping of the size of the log records (binary data) and the DML commands as results from the log records (text). And, of course, the question is where the distributor is running.
And the final question is where the subsriber is. So, you should draw a picture with the involved databases, where you have network between the databases and where the Agent jobs are running.
And there's more: Some operations aren't carried over with transactional replication. One example is index rebuild. It can generate massive amount of log records (if full recovery model), but such an operation isn't executed on the suscriber - so it is ignored by the log reader.
And since you say subset, if you mean subset of the database: How would you, from the log, determine which log records apply to tables/rows that are in this subset?
I'm mainly raising issues here, just to point out how easy you can fall into a too simplified &quot;solution&quot; for this which can be misleading. This MS article talks about a few options that can be usable, when you have implemented replication.
Disclaimer: I'm no replication expert, so feel free to correct me, y'all.