title: SQL Server SPIDS go into a sleeping state and never recover
body: <p>I have a long running stored procedure that is executed from IIS. On average this stored procedure takes between two and five minutes to complete because it is searching through a large dataset. (although it has take around 20 minutes in some cases)</p>
  <p>Most of the time the stored procedure works fine but every now and then the SPIDS go into a sleeping state and never recover. The only solution I have found is to restart the SQL Server and re-run the stored procedure</p>
  <p><a href="https://i.stack.imgur.com/hZh9e.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hZh9e.png" alt="enter image description here" /></a></p>
  <p>The are no table inserts in the proc (only table variable inserts), and the other statements are selects on a large table.</p>
  <p>I'm stuck for where to start debugging this issue. Any hints one what it might be or suggestions on tools that would help me find the issue would be most helpful</p>
  <p>EDIT: More info added:</p>
  <ul>
  <li><p>The actual issue is the proc doesn't return the resultset. My first    thought was to look at the spids, they were sleeping but the cputime    was still increasing</p>
  </li>
  <li><p>It's a .Net app so .Net Core 3.1 with ASP.NET Core and a Blazor UI.    The libary used for db connection is System.data.SqlClient I believe    System.data.SqlClient uses it's own custom driver. Calling code    below:</p>
  </li>
  </ul>
  <p><a href="https://i.stack.imgur.com/oN9U5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/oN9U5.png" alt="enter image description here" /></a></p>
  <ul>
  <li><p>The stored procedure doesn't return multiple result sets, however obviously different instances of the proc run at the same time.</p>
  </li>
  <li><p>No limits to connection pooling in IIS</p>
  </li>
  </ul>
  <p><a href="https://i.stack.imgur.com/S3sCP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/S3sCP.png" alt="enter image description here" /></a></p>