SQL Server Log File Rapidly Growing

Kmcnet 696 Reputation points
2023-09-29T13:33:13.8433333+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,851 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Vahid Ghafarpour 18,450 Reputation points
    2023-09-29T17:28:52.3833333+00:00

    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.

    0 comments No comments

  2. Rahul Randive 8,826 Reputation points Microsoft Employee
    2023-09-29T17:48:32.9733333+00:00

    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.

    Transaction log grows due to long-running transactions when you use Change Data Capture - SQL Server & Azure SQL

    related blog- https://www.mssqltips.com/sqlservertip/4666/long-running-transactions-cause-sql-server-transaction-log-to-grow/

    Thank you!


  3. Erland Sommarskog 102.2K Reputation points MVP
    2023-09-29T20:25:20.2366667+00:00

    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.