title: SQL Server FULL TEXT search session locked forever
body: <p>we're dealing with a very strange strange issue that happens randomly (only last month after years of no-issues):</p>
  <p>Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)</p>
  <p>Basically our application executes a SQL FULL TEXT query using the &quot;FREETEXT&quot; T-SQL statement in order to search a full text catalog of millions of records and &quot;joins&quot; the result with another table.</p>
  <p>Now, depending on the searched text, the query can execute in 300ms but can last more than 30-50 seconds. The CommandTimeout is set to 60 seconds: However, it happened 2 times out of 10-20k of similar requests that the query ran for more than 13 HOURS... leading the system to lock progressively due progressive lock increase.</p>
  <p>Analyzing the waiting tasks when this happened, we found the session were waiting for
  PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY</p>
  <p>This is an undocumented lock type...</p>
  <p><a href="https://i.stack.imgur.com/J0Apn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/J0Apn.png" alt="Wait Tasks" /></a></p>
  <p>Trying to kill the session the same remained in KILLED/ROLLBACK for more than 45 minutes, and the session remained &quot;Estimated rollback completion 0%, Estimated time remaining 0&quot; for all this time.</p>
  <p>The only solution WAS TO RESTART THE SQL SERVER... after doing that the issue disappeared.</p>
  <p>The environment is customer Production environment where we do not have access.</p>
  <p>Now, we're trying to understand the best way to investigate the issue and/or a possible solution to these locks:</p>
  <ul>
  <li>Since we're dealing with FT, it's a sort of distributed query: could this lead to timeouts not being managed correctly in certain cases?</li>
  <li>Is this a possible bug caused by some kind of &quot;dirty data&quot; on the database?</li>
  <li>Is there something that we can do in order to avoid not being able to terminate/kill this session?</li>
  </ul>
  <p>What bugs me is actually &quot;How can a command run for more than 13 hours if the CommandTimeout is set to 1 minute?&quot; ... it's a nonsense: maybe the reason is to be found on a glitch in the syncronization of SQL-FT process interface?</p>
  <p>Thank you in advance for any help.</p>