December 5, 2018 at 12:39 pm
Hi, I have an hourly process (1) that runs a query against a view (X) that unions results from tables A and B. The query typically takes around 30 seconds. We also have a monthly process (2) that updates table A and truncates table B.
By chance, the monthly process happen to hit a point where it wanted to truncate table B while my hourly process was running. The monthly process received a LCK_M_SCH_M wait since the hourly process was accessing table B. Process 2 also showed it was blocked by Process 1. 3 hours later someone noticed the block was still in place and process 1 was still running. They killed Process 2 and restarted it later. Process 1 still did not finish. The next morning Process 1 was still running and seemed to be stuck in limbo. My 30 second query had been running for over 12 hours. Once I killed process 1, process 2 was able to complete.
It seems when the LCK_M_SCH_M wait was applied to process 2 it somehow affected process 1 that was already running against the table. Any theories or is this a bug?
97 = Process 1
76 = 2nd run of Process 2
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply