All we need is an easy explanation of the problem, so here it is.
Currently in my database server is too much slow. Running,
EXEC sp_WhoIsActive @find_block_leaders = 1, @sort_order = '[blocked_session_count] DESC'
Sometimes we get below error,
Msg 530, Level 16, State 1, Procedure sp_WhoIsActive, Line 4316 [Batch Start Line 17] The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
and when it runs, blocked_session_count to higher number but blocking_session_id is NULL. There is something is blocking and I am unable to find it out.
SELECT COUNT(*),log_reuse_wait_desc FROM master.sys.databases group by log_reuse_wait_desc
returns | 60 | LOG_BACKUP|
When I do failover it starts working but I cannot keep doing failover.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Lead blockers are often not being blocked — they’re causing the blocking because they’re running for a long time waiting on something else, or sleeping and waiting for another instruction. During that time they’re holding locks. Note that lock waits come from blocking, not locks being taken.
WhoIsActive only orders (in your scenario) by which sessions have the most blocked sessions under them. They could be entirely unrelated. What you’re probably looking for is something that walks the blocking chain to show you the lead blocker, and then all of their blocked sessions.
Some helpful scripts for that are here:
- Identifying Blocking Chain Using sp_WhoIsActive
- Look at Blocked Process Reports Collected With Extended Events
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂