August 9, 2024 at 9:04 pm
Hi,
I have some interesting issue.
Server1
2024-08-09 02:59:25- start session 234 with UPDATE STATISTICS on Table1 WA_sys_xxxx stat
2024-08-09 03:00:08 - start session 800 - with ALTER INDEX on Table1 with Index1
2024-08-09 03:00:25 - deadlock session 800 like victim and session 527, which is application with some MERGE
2024-08-09 03:04:22 - first record in XE blocking process between 800 and 527
2024-08-09 03:08:04 - reported first connection issue, Network error code 0x2746 occurred while establishing a connection , lot of transaction which is waiting with HADR_SYNC_COMMIT
2024-08-09 03:12:06 - finished Job from Application
2024-08-09 03:13:32 Last commit time from Server2 - SYNC replica
2024-08-09 03:14:09 Last commit time from Server3- SYNC replica
2024-08-09 03:14:54 Last commit time from Server4- ASYNC replica
2024-08-09 03:21:28 - Process 234 was killed from session 800 with ABORT_AFTER_WAIT = BLOCKERS from Ola script
2024-08-09 03:26:00 - from collecting exec_dm_request was session 800 in rollback
So it was change to rollback after 25minutes, because deadlock was at 03:00:25 and rollback I catch at 03:26:00 on Index, before was suspended ...
Do you have please idea why? What could happened? Cpu was down, because all sessions were with HADR SYNC COMMIT wait.
And Transactions increased so much and then not possible to connect to SQL with SSMS.
So I fixed only with restart SQL service.
But it was happen something similar second time, so I am curious little bit.
Thanks
August 10, 2024 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 10, 2024 at 10:53 pm
Is the secondary readable? If so, was there anything else going on there?
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply