Timeline for Database Blocking, kill the session which is responsible after 5 minutes
Current License: CC BY-SA 4.0
16 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 6, 2020 at 15:07 | history | edited | LowlyDBA - John M |
edited tags
|
|
| Dec 6, 2020 at 1:03 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Nov 4, 2020 at 11:33 | answer | added | Harshad | timeline score: -4 | |
| Nov 4, 2020 at 2:23 | answer | added | J.D. | timeline score: 2 | |
| Mar 19, 2020 at 13:02 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Nov 17, 2019 at 7:01 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jul 13, 2019 at 11:02 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Apr 8, 2019 at 14:49 | comment | added | Learning_DBAdmin | @JohnEisbrener Mostly lead blocker is holding IX/IU/X locks. They are either delete, update or insert operation followed by other queries which will also be performing one of DML operation. | |
| Apr 8, 2019 at 14:46 | comment | added | John Eisbrener | If possible, listing the queries of the lead blocker as well of a few of the blocked queries would be helpful as well. | |
| Apr 8, 2019 at 14:37 | comment | added | Learning_DBAdmin | @JohnEisbrener Updated the question with isolation level, as you could see in the screenshot - writer is blocking writer and there are lot of foreign keys in the table. That seems to be the culprit. Since, Its taking too long to check and fix, I am looking for some script/procedure to find the blocking spid and kill it if it stays on the server for more than 5 minutes.(Not a very good approach though but, can't hep it). | |
| Apr 8, 2019 at 14:31 | comment | added | John Eisbrener | So READ COMMITTED SNAPSHOT is enabled and you're still getting this behavior? Can you update your question with this information and then provide some more context around the queries causing this behavior? | |
| Apr 8, 2019 at 14:18 | history | edited | Learning_DBAdmin | CC BY-SA 4.0 |
Added details about Database isolation level.
|
| Apr 8, 2019 at 14:14 | comment | added | Learning_DBAdmin | @JohnEisbrener That was the first thing I did after joining this company, change isolation level from default to read committed snapshot. | |
| Apr 8, 2019 at 13:59 | comment | added | John Eisbrener | Have you looked at optimistic isolation levels such as READ COMMITTED SNAPSHOT or SNAPSHOT ISOLATION which may help avoid these large chains of blocking queries outright? These changes do have caveats though, so do some investigation and testing in non-production environments before making any changes to your live system. | |
| Apr 8, 2019 at 11:46 | answer | added | user164767 | timeline score: -2 | |
| Apr 8, 2019 at 11:39 | history | asked | Learning_DBAdmin | CC BY-SA 4.0 |