[SQL] Why is my transaction log full of LOP_LOCK_XACT?

I have recently experienced a strange issue with one of our sql server staging databases, whereby the transaction log and log backups suddenly grew around a hundredfold in size, causing a disk space issue within the space of a couple of hours.

The server is SQL2019 and the database uses FULL logging mode. We take a full backup daily and log backups hourly. Ordinarily the log backups are around 250MB in size, but at the time of the issue they jumped to around 25GB, and the transaction log grew from around 15GB to over 200GB at which point the disk filled up.

Searching through SQL server logs and the application logs from the java application that is interacting with the database did not show any obvious answer so I resorted to using the MS fn_dump_dblog function to try and look at the transaction log backups

What I found was that 99.9% of the whole log backup was LOP_LOCK_XACT operations, over 2 billion such operations in one hours worth of log backup. Most of the columns retrieved for these operations are NULL, there is no allocation unit value with which to link it to a table.

Every such row appears to pertain to the same transaction and the LSN's are all concurrent. The Lock information appears as 'HoBt 0:ACQUIRE_LOCK_IX ROWGROUP: 0:72057594066239488:7'

Looking at 'healthy' log backups in comparison there are still a lot of LOP_LOCK_XACT entries but they are matched with other corresponding operations such as LOP_BEGIN and LOP_COMMIT

I'm a bit stuck on figuring out what could cause such behaviour, it's as if lock requests are being spammed by the connecting applications but without an allocation id I am unsure as to figure out what the lock request is for or why

Any help would be appreciated.

Continue reading...