LCK_M_SCH_M affected running query

• Scott Leseman

  SSC Enthusiast

  Points: 146

  More actions

  December 5, 2018 at 12:39 pm

  #378781

  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