Finding Redo thread blocker in AGs

Tomáš Zíka 256 Reputation points
2021-09-15T11:41:13.617+00:00

I'm looking at the Extended events session AlwaysOn_health.
One of the events is lock_redo_blocked and I'm trying to find the blocker.

First of all, I haven't found any documentation for this event. The only info I've found was in sys.dm_xe_object_columns and mentioned some of the columns.

I'm trying to figure out what does the resource_0 refer to in this picture:
132395-image.png

the number is a bigint so it rules out object_id (which is int). I also haven't been able to match it to either partition_id or hobt_id.

Is there any way I can find the blocker after the fact?

I don't have data from sys.dm_exec_requests at that time (as advised here: troubleshoot-availability-group-exceeded-rto)

Thank you

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,767 questions
0 comments
Accepted answer
  1. Tomáš Zíka 256 Reputation points
    2021-09-16T12:33:55.393+00:00

    I have figured it out. When I add an addition Event field resource_description to the lock_redo_blocked Extended event (which is disabled as default)

    132670-image.png

    It will collect additional info that helps me point to the metadata's parent object.

    132758-image.png

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2021-09-15T22:07:02.3+00:00

    Finding the blocking process after the fact is of course impossible. SQL Server does not record that to file or something. You need to set up some tracing to capture that, for instance with the blocked process report.

    As for the resource id, did you try sys.alllocation_units?

    1 person found this answer helpful.