Can Sql Server go unresponsive for hours and then start working again?

Question

Using Microsoft Sql Server 2019 express edition.

Have it installed and running on my machine. Randomly, it will go non responsive for hours, and then with me doing nothing, just start working again.

I have looked in the Sql Server logs and don't see anything that stands out.

Anybody have an ideas on this ?

Answer 1

When I say non responsive, I mean that all attempts to insert data or read data result in command timeouts. Then after several hours, I can insert/read data.

When this happens, try to open a query window, and run sp_who2 while you have request running.

I would not be surprised if you find a row with a value in the Blk column. In that case, this the spid of the process that is blocking the process on this line. It sounds as if you have an uncommitted transaction somewhere, maybe in a query window.

Answer 2

Hi @Darryl Hoar，

Sounds like a block issue.

To counteract the difficulty of troubleshooting blocking problems, a database administrator can use SQL scripts that constantly monitor the state of locking and blocking on SQL Server. To gather this data, there are two complimentary methods.

The first is to query dynamic management objects (DMOs) and store the results for comparison over time. Some objects referenced in this article are dynamic management views (DMVs) and some are dynamic management functions (DMFs).

The second is to use Extended Events(XEvents) or SQL Profiler Traces to capture what is executing. Since SQL Trace and SQL Server Profiler are deprecated, this troubleshooting guide will focus on XEvents.

Details you may see Understand and resolve SQL Server blocking problems

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.