Timeline for Why does a create trigger (that already exists) block all processes in SQL Server 2016 SP3?
Current License: CC BY-SA 4.0
6 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 31 at 6:03 | comment | added | Ted Cohen | Adam beat me to it. He already coded the logic that I described. A user turns it on using @find_block_leaders. I just found it when I went to study my code to see if it was possible for it to produce incorrect results due to a process id being reused. I found his. Thank you for your comment J.D. I will be using Adam's code going forward. | |
| Oct 31 at 0:12 | comment | added | Ted Cohen | That is not something that I have explicitly considered. My logic, naively assumes that I can follow each blocked process to the point where the last process in the chain is not blocking anyone. My theory is that each process along the way is it self blocked and so it can not be reused until the "head of the snake" is killed and the logjam is freed up. I will study my code. In general, refreshing the query can add more blocked processes but the process ids that were suspended remain there till we either kill a process or go find a process that is waiting on user input and provide it. | |
| Oct 30 at 20:07 | comment | added | J.D. |
@TedCohen "I augmented it by chasing every process counting all of the process being blocked by all of the processes being blocked all the way to the end of every chain" - If you're using the ProcessId and BlockedProcessId to recursively get that list, be wary that this methodology may return incorrect results sometimes due to the timing of execution and when a ProcessId may be recycled and used for a new process.
|
|
| Oct 30 at 4:37 | comment | added | Ted Cohen | Maybe this should be a separate question, but given that sp_whoisactive is showing us the trigger that is blocking, could we (or Adam) modify spwhoisactive to show the DML (query) that triggered the trigger in addition to the trigger that is holding the lock at the head of the chain? | |
| Oct 30 at 3:56 | comment | added | Ted Cohen | I had no idea that it was executing the trigger instead of creating it which made no sense. This gives me an entire new perspective. I will look at what is going on with new eyes FWIW, I built my "who" stored procedure based on sp_WhoIsActive. I augmented it by chasing every process counting all of the process being blocked by all of the processes being blocked all the way to the end of every chain then I sort the processes in order of who is blocking the most processes. I will wait until it happens again and then examine the locks being held and the processes waiting on them. Tx!! | |
| Oct 30 at 0:37 | history | answered | HandyD | CC BY-SA 4.0 |