Do any of the DMVs or is there another way to expose what kind of data is in the Redo Queue that's in process of being synced up to a secondary replica? (E.g. is it table data and which table, or is it index changes and which indexes, etc?)
SQL Server – Determine Data in Redo Queue for AlwaysOn AG Secondary Replica
availability-groupsdata synchronizationreplicationsql serversql-server-2016
Related Question
- SQL Server AlwaysOn – DDL and Schema Changes
- SQL Server – Always On DDL Operations Explained
- SQL Server – Difference Between High Availability Data Synchronization and DAG
- Sql-server – CMEMTHREAD waits during Index Optimisation in Availability Group
- Sql-server – SQL Server – Log file not shrinking in AlwaysOn group
- SQL Server – High Redo Queue Size and Estimated Recovery Time in AlwaysOn AG Secondary Replica
- Sql-server – How to debug a growing Redo Queue Size in the Availability Group Dashboard
Best Answer
The short answer is no, there is not a DMV that contains this information. It would be a significant amount of overhead for SQL Server to track what table was associated with each log record that had been received but not committed.
As you can read in Reading the transaction log in SQL Server – from hacks to solutions, you can read the log records with a function, but it's certainly not something that you would want to do in production just to have some interesting information. Doing something like this on every log record in real time would add a tremendous amount of I/O load on a busy server, and you wouldn't want it to be done unless it solved a significant problem.
Although the type of information you're looking for would be interesting, you might get a better answer if you can state why you want to see this information--there may be a better solution.