SQL Server 2019 Table lock and wont unlock until restarting database

Question

We are running Microsoft SQL Server 2019 (RTM-CU32-GDR) and a specific database table seems to lock and never unlocks. It also locks all associated tables. The only way to get the database to respond is wait for it to restart or restart the entire SQL Server. Any else have this issue?

Answer 1

Addition to @Erland Sommarskog solution, you may try following:

SELECT session_id, blocking_session_id, wait_type, wait_resource, status, command
FROM sys.dm_exec_requests WHERE session_id > 50
ORDER BY blocking_session_id DESC;

If blocking_session_id is populated, it's a blocking chain. If it's all zeros and everything is still slow, it's a resource problem, not a lock.

If blocking, find the head:

SELECT s.session_id, s.status, s.open_transaction_count, s.host_name, s.program_name, ib.event_info
FROM sys.dm_exec_sessions s
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, NULL) ib
WHERE s.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0);

Sleeping with an open transaction means an app left a transaction uncommitted. KILL <spid> clears it.

To catch it next time:

EXEC sp_configure 'blocked process threshold', 15; RECONFIGURE;

Then an Extended Events session on blocked_process_report.

Answer 2

probably some application (commonly Excel) is taking locks and not freeing them. as suggested use sp_who and sp_lock to find the spid that is blocking. instead of a restart, you can just kill the offending spid.

Answer 3

Apparently some process in your system is taking a lock on this table.

A simple way to troubleshoot this is to run sp_who or sp_who2. Keep an eye on the blk (sp_who) or BlkBy (sp_who2) column. A non-zero value in this column means that the session on this row is blocked by the session in the blk/BlkBy column. A KILL on that spid will get things going again.

You can also use Activity Monitor in SSMS to inspect this, and it may give you information what that blocking process is doing. I rarely use Activity Monitor myself, so I am not sure exactly what you see.

Yet an alternative is to use my beta_lockinfo, which gives you a lot of information about activity in the system, include blocking and the current batch and current statement of each process. Look for rows with double exclamation marks, !!, that is your lead blocker.