CHECKPOINT had locked by COMMIT_TABLE wait type on a database with CT enabled

Question

Hello!

On the following server

Microsoft SQL Server 2019 (RTM-CU19) (KB5023049) - 15.0.4298.1 (X64) 
	Jan 27 2023 16:44:09 
	Copyright (C) 2019 Microsoft Corporation
	Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core)) <X64>

we have a DB with enabled CT and about 90 tables with CT enabled.

At some point query to CT got killed and stuck with EXECSYNC wait type while doing ROLLBACK.

But the main problem was with the CHECKPOINT process which got blocked by the COMMIT_TABLE wait type and did not proceed for days, making the log file bigger and bigger. There had been 30 GB of dirty pages in memory.

I found on the web that the COMMIT_TABLE is related to the in-memory part of the table used by CT.

Eventually, we restarted the whole server and had to wait for a few hours for DB to recover. Now we need to enable CT back but I am afraid to get into the same situation.

Does anybody see this before?

Thanks!

Answer 1

This is not a situation that I have experience of, but to me is sounds like something that shouldn't happen. That is, it's a bug. So you could open a support case with Microsoft, but since the situation has been resolved, I don't think there is much they can analyse. What they can do is to give you a package that collects data that they can analyse if the issue re-occurs.

Then again, it seems like this was an unfortunate chain of events, so you may not run into these again.