Log growth in simple recovery model

Mario2286 441 Reputation points
2021-02-11T17:03:22.53+00:00

My database is in simple recovery mode and log is growing very fast and space in disk is very less. There are 2 transaction is running in My database and the first transaction is running 20 mins earlier than second transaction. Both transaction still running and didn't release the log space due to active transaction. When i check, i found out that second transaction using more logs than first transaction eventhough first transaction started first. In this case, can i kill second transaction which using more log space to free up some space or i must kill the oldest transaction first which is first transaction to release log space.

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

Accepted answer
  1. Erland Sommarskog 103.5K Reputation points MVP
    2021-02-11T22:27:06.493+00:00

    The transaction log will never be truncated past the oldest active transaction. That transaction may have been left orphaned and is doing nothing, but that does not matter. SQL Server is not able to selectively delete log space for committed transaction, so it has to keep the entire log so that it can roll back this open transaction if needed.

    1 person found this answer helpful.
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. CathyJi-MSFT 21,106 Reputation points Microsoft Vendor
    2021-02-12T02:56:53.867+00:00

    Hi @Mario2286 ,

    A long-running query, no matter how much work it’s done (and hence how many log records it has generated) will prevent the log from clearing, as the log all the way back to the LOP_BEGIN_XACT log record of the long-running transaction is required, in case that transaction rolls back. And the log will not be able to clear until (at least) that long-running transaction has committed or finished rolling-back.

    If the long-running query has generated a lot of log records, then it’s going to take a long time to roll back. That rollback itself won’t cause the log to grow any more, as a transaction always reserves free space in the log to allow it to roll back without requiring log. However, as it will take a long time to roll back, other activity in the log from other transactions will likely cause the log to grow more until it’s finally able to clear.

    Under the simple recovery model, an automatic checkpoint is also queued if the log becomes 70 percent full. Then the log will be truncated. Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the physical transaction log. But it will not reduce physical transaction log size, until you shrink log file.

    Refer to the blog Should you kill that long-running transaction?


    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    1 person found this answer helpful.
    0 comments No comments

  2. Tom Phillips 17,716 Reputation points
    2021-02-11T17:06:55.83+00:00

    Do not confuse the log physical size with the log reuse inside the log file.

    All transactions are logged to the log file. The size of the log file must accommodate the entire transaction, regardless of recovery mode.

    The physical size of the log file will never get smaller unless you manually run a shrink.

    0 comments No comments

  3. Mario2286 441 Reputation points
    2021-02-12T02:11:53.57+00:00

    Thank you @Erland Sommarskog that means I must kill oldest transaction which is first transaction eventhough it use less log space. No point i m killing second transaction, it still wont release log space until i kill oldest transaction. Am i correct?