SQL Server 2019 and Transparent Data Encryption - Intermittent (silent) Error Log Messages

Question

I recently migrated a SQL Server 2017 TDE database to SQL Server 2019 CU4. I was browsing the Error Log and noticed intermittent messages about

An error occurred while processing log encryption. The process was recovered automatically. No user action is required.

These messages are always preceded by

Error: 1222, Severity: 16, State: 55.

And

Lock request time out period exceeded.

I found FIX: TDE encrypted Databases go in suspect state during the recovery phase when you restart SQL Server, where it discusses a fix that has been back-ported to previous versions of SQL Server.

From that post

Assume that you have a Transparent Data Encryption (TDE) encrypted database on an instance of Microsoft SQL Server. When you restart the instance of SQL Server, you may receive a lock time-out error that resembles the following: Starting up database . Error: 1222, Severity: 16, State: 55. Lock request time out period exceeded. Error: 9016, Severity: 21, State: 7. An error occurred while processing the log for database . The log block could not be decrypted. Then, the database will go in a suspect state.You can get the database back online by using Emergency Mode. You won't encounter corruption.

I'm not in the situation where my instance is being restarted, but it seems this fix is similar to what I am experiencing.

While I appreciate the process being automatically recovered, I'm concerned why the error is even showing up in the error log.

I also have an almost identical instance in the same migration scenario from SQL Server 2017 to 2019 that is NOT experiencing these errors.

Answer 1

Thought I would contribute this answer for anyone else arriving here from a search engine as I did.

I had exactly the same issue with the same error on the same version of SQL Server 2019 (albeit on CU28).

In our case, after investigation of dm_os_ring_buffers we identified that the operating system (Windows Server 2022 Datacenter) was experiencing memory pressure (source).

SQL Server was configured on a 64GB machine to use 60GB of RAM, leaving 4GB for Windows. We adjusted the max memory settings down to 56GB to leave 8GB for Windows and the issues with the log stopped.

This also solved an issue we were experiencing with our plan cache being erased every four hours or so.