Why does sys.dm_db_log_space_usage.used_log_space_in_bytes keep increasing with Simple recovery model

Hong 1,546 Reputation points
2026-08-24T09:51:18.3466667+00:00

I have a simple database with essentially only one active table, though it is a large one. It is for app analytics. A bunch of records are inserted every few seconds. It is searched a few times every day. That is all of its use.

Its recovery model is Simple, but the occupied size of the log (sys.dm_db_log_space_usage.used_log_space_in_bytes) keeps growing. I checked it every day for a few days. Here are the values of sys.dm_db_log_space_usage.used_log_space_in_bytes:

21MB

33MB

44MB

54MB

I thought that the server would reuse its log space with the Simple recovery model even when auto-shrink is disabled. Could anyone shed some light on this?

User's image

SQL Server Database Engine
0 comments
Answer accepted by question author
Erland Sommarskog 137.1K Reputation points MVP Volunteer Moderator
2026-08-28T20:20:43.3366667+00:00

Hm, I wonder if the memory-optimized filegroup has something to do with the log behaviour. Could be a bug that has been fixed in SP3.

No, you cannot remove a memory-optimized filegroup. Or, well, you can, if you upgrade to SQL 2025 - they have finally implemented that!

Do you actually have any memory-optimized tables?

To resolve the issue, you would have to create a new database and copy data over. And don't add any filegroup for memory-optimized data this time. :--)

Was this answer helpful?

1 person found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bruce (SqlWork.com) 85,201 Reputation points
    2026-08-27T14:37:59.46+00:00

    With simple recover mode, the log data is released on checkpoint, but the disk space is only reused, not released. The log will be the size of the largest transaction. Say you updated every row, the log would large.

    Was this answer helpful?

    0 comments
  2. Marcin Policht 105.8K Reputation points MVP Volunteer Moderator
    2026-08-24T11:11:31.5766667+00:00

    Yep - AFAIK, with Simple recovery, SQL Server normally reuses log space after checkpoints. Disabling Auto Shrink does not prevent this. Auto Shrink controls whether SQL Server physically reduces the size of the .ldf file.

    Check:

    SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'YourDatabaseName';

    You want log_reuse_wait_desc to be NOTHING. If it says ACTIVE_TRANSACTION, for example, an application transaction may have been left open and is preventing log truncation.

    Also, your continuous inserts can cause the log to grow. Every insert is logged, including changes to indexes. Simple recovery doesn't eliminate logging - it allows inactive log records to be reused.

    So 21 MB → 33 MB → 44 MB → 54 MB is worth investigating, but don't enable Auto Shrink or manually shrink the log yet. First check log_reuse_wait_desc. That value should tell you what is preventing reuse.

    If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

    hth

    Marcin

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.