I am trying to find out the amount of transaction-log data generated by SQL Server 2016 during every hour or every day. By "data generated" I mean how much data (in bytes/KBs/etc) was written to disk every hour (or every day).
Is there a way to find this out?
Our database is in FULL recovery mode and we do have regular transaction log backups. So, I am on the opinion that querying the backup metadata in msdb may help to achieve that. Does it work ? Will it give me correct and reliable results?
Second option will probably be to look at the amount of read/write IO that is happening to the transaction log files. Can this one work? If yes, how can I do that? Are there any SQL Server DMVs providing such information? What about Windows tools? (such as Windows performance monitors)?
If possible at all, I would prefer the second option above because it won't require having transaction log backups. Therefore, it can be used even with SIMPLE recovery model databases. So, my question is, is it possible?
Are there any other alternatives? Such as SQL Server tools or views that are readily available?
Please note, I need this data because we are trying to estimate the amount of network IO that will be required if we create a (near) real-time replication of our databases in the cloud. So, I thought we should somehow measure the amount of IO attributed to the transaction log files. Is my assumption correct that the required IO will be equal to the amount of Io to the transaction log files? Is this how SQL Server transaction replication work ? (i.e. by sending VLFs to the replicated site)?