This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(208, 34%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EK%3C/text%3E%3C/svg%3E)
Hello everyone and thanks for the help in advance. I have a stand-alone SQL Server 2019 running on a Windows Server 2022 machine. The log file for the database has started to rapidly grow in the last week and I am concerned I will soon run out of space. The event viewer shows numerous Event ID 847 message "Timeout occurred while waiting for latch: class 'LOG_MANAGER', id 0000020D9E120BC8, type 4, Task 0x0000020D925AB088 : 0, waittime 900 seconds, flags 0x1a, owning task 0x0000020DA7ADB848. Continuing to wait." Also seeing Event ID 9646 "An error occurred in the timer event cache. Error 11743, state 14." and Event ID 11743 "Timeout occurred while waiting for METADATA_SEQUENCE_GENERATOR latch: waittime 120 seconds.". Also seeing Event ID 5145 "Autogrow of file 'myDb_Log' in database 'myDb' took 1483781 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.". Also receiving error from one our applications that utilize Entity Fraemework giving occasional error "Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.". Not sure what to do with all of this.
Review the recovery model of the database. If it's set to "Full" recovery, consider whether it's necessary for your application. Changing to "Simple" recovery can reduce the amount of log data generated.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(236.8, 79%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERR%3C/text%3E%3C/svg%3E)
Hi @Kmcnet
The SQL Server Database Engine writes a log record for every single operation in the database, that includes starting or ending a SQL transaction when a data modification process is performed, when a database table or index is created or dropped, and after each page allocation or deallocation operation.
In the heavily transactional systems with excessive logging operations, the SQL Server Transaction Log file will grow rapidly, until it reaches its maximum size, and if the auto-growth option is enabled, the underlying disk drive will run out of free space.
Please check if any long-running or uncommitted transaction, such as archiving and purging transactions running on your server
To overcome the uncommitted transaction issue, make sure to write complete transactions that are handled in case of success or failure and monitor the orphaned distributed transactions that stuck without being committed.
Please find the document and queries to find the long running transaction.
related blog- https://www.mssqltips.com/sqlservertip/4666/long-running-transactions-cause-sql-server-transaction-log-to-grow/
Thank you!
Thanks for the response. i have not tried applying the cumulative update as suggested below yet. I am unsure how to monitor complete transactions. However, I did notice a .Net application that uses SQL Notification that has now occasionally started to fail. Upon that failure, the log grew nearly 2 TB. When the application fails, it appears the notifications stop being picked up and processed. Restarting the application clears the problem. The server is also running several SignalR applications. However, all of these applications have been running for several years without problems.
Now just received from another application, "System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 59) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim."
However, all of these applications have been running for several years without problems.
And? Does that mean you think there is no need to apply the CU? As I said, apply the CU, and if it never happens again, just forget about it. If the issue re-occurs, you need to conduct more troubleshooting.
Now just received from another application, "System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 59) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim."
Yes, that's a deadlock. It's perfectly normal for a system to have occasional deadlocks. If you have that many that they actually cause issues in the system, you need to troubleshoot them.
In any case, you are running an outdated build of SQL 2019, so go and apply CU23 without delay.
I will certainly apply the CU. My concern is trying to head off any unintended consequences of doing so before proceeding. Perhaps I am being overly cautious, but have been bitten too many times with updates. Are you saying this log problem may have just started out of the blue even though I have been running this version for a while?
You get some mysterious errors about latches which very well could be due to a bug in the product that has been fixed. Yes, occasionally a CU may introduce a regression, but on average CUs fix more issues than they introduce.
First of all, what does "SELECT @@version" report?
Next, given all the error message you see, I would absolutely restart SQL Server. The error messages you see are not exactly normal, and I would not expect that there is a simpe way to resolve them without restarting SQL Server.
After a restart, things should go back to normal, and the log wlil stop growing. And if the messages never come back, forget that it ever happened. If the messages reappear after some time, open a case with Microsoft to track down what is happening.
But before you go there, make sure that you have the most recent Cumulative Update installed, and that's why I asked the first question.
Thanks for the response. Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2022 Standard 10.0 <X64> (Build 20348: )
As I feared. That is the release version. You should immediately apply Cumulative Update 23, which is the most recent. The reason I say "immediately" is that you need to restart SQL Server, so you may apply the CU while you are at it anyway.
It is not at all unlikely that you are victium to a bug that has been fixed.