Sql-server – SQL server is getting stuck all of a sudden

performancesql server

I manage a virtual SQL server that holds several databases for some applications that run in a front-end server. The issue I'm going through is that, at a specific moment during the day, the server gets stuck and the instance becomes unreachable. I can RDP the server but I cannot connect neither from SSMS nor from the application. I have searched into the OS logs and into the SQL server error logs but I find nothing relevant that could give a hint about what is causing the SQL server to become unresponsive. As a consequence, I have to do a stop-and-start process with the SQL server service and after that the instance will become available once again.

Anyone who might have experienced this in the past? Thanks.

Best Answer

Try running sp_Blitz, a free health check for your SQL Server (disclaimer - I'm the author). You can also run it with @OutputType = 'markdown' if you want to share the results here at Stack.

I've got a hunch that you're running into THREADPOOL waits, and sp_Blitz alerts about that. THREADPOOL means your SQL Server ran out of worker threads to service incoming queries. It won't show up in the OS or SQL Server error logs.

When it's happening, you'll be able to connect to SQL Server using the Dedicated Admin Connection (DAC) (disclaimer: that's a blog post on my site.) The DAC is a set-aside CPU scheduler used just for emergency troubleshooting. From there, you'll be able to see which queries are burning up all the worker threads - typically it's a blocking problem.