What is log_reuse_wait_desc and What Does It Mean?
The log_reuse_wait_desc column in the sys.databases system table indicates why free space in a database's transaction log file cannot be reused.
If your transaction log file is constantly growing or not being cleared, this column is the most important resource to understand why.
| Value | log_reuse_wait_desc | Explanation |
|---|---|---|
| 0 | NOTHING | Reusable virtual log files (VLF) are now available. |
| 1 | CHECKPOINT | No checkpoint has occurred since the last log shrink. |
| 2 | LOG_BACKUP | To reduce the size of the transaction log, a log backup must be taken. |
| 3 | ACTIVE_BACKUP_OR_RESTORE | A data backup or restore operation is in progress. |
| 4 | ACTIVE_TRANSACTION | There is a transaction in progress and log shrinking is being blocked. |
| 5 | DATABASE_MIRRORING | Database mirroring has been paused or lagged. |
| 6 | REPLICATION | Log cannot be shrunk due to transaction replication. |
| 7 | DATABASE_SNAPSHOT_CREATION | A database snapshot is being created. |
| 8 | LOG_SCAN | Log scanning is in progress. |
| 9 | AVAILABILITY_REPLICA | The Always On Availability Group replica is applying log records. |
| 10-12 | (Not in use) | Reserved for internal use. |
| 13 | OLDEST_PAGE | Due to the indirect checkpoint, the oldest page delays log shrinkage. |
| 14 | OTHER_TRANSIENT | This value is not currently used. |
| 16 | XTP_CHECKPOINT | A checkpoint operation is required for Memory-Optimized Tables. |
log_reuse_wait_desc How Can I See Its Value?
You can check its value with the following query log_reuse_wait_desc:
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'databaseName';
log_reuse_wait_desc What Should Be Done To Solve Your Problem?
LOG_BACKUP → Take a log backup (if you are using FULL Recovery)
BACKUP LOG [databaseName] TO DISK = 'C:\Backup\databaseName.trn'
ACTIVE_TRANSACTION → Find the long-running transaction and terminate it if necessary
DBCC OPENTRAN('databaseName'); -- See the open transactions
KILL <SPID>; -- Terminate long-running process (proceed with caution!)
CHECKPOINT → Run Manual Checkpoint
CHECKPOINT;
Clean (Shrink) the LOG File
DBCC SHRINKFILE (databaseName_Log, 100); -- Reduce the size into 100MB
In case of AlwaysOn and Mirroring, Check Sync
SELECT * FROM sys.dm_hadr_database_replica_states WHERE database_id = DB_ID('databaseName');
log_reuse_wait_desc: OLDEST_PAGE What Does It Mean?
If log_reuse_wait_desc its value OLDEST_PAGE is , this means:
The transaction log cannot be cleared because the Lazy Writer process does not write the oldest 's to disk.dirty page
What is a Dirty Page (Dirty Page)?
In SQL Server, when data pages are modified in memory but have not yet been written to disk, it is called a "dirty page."
OLDEST_PAGE Reasons
Disk I/O Slowness:
- SQL Server may be experiencing a delay while writing changed data to disk.
- You should check for disk bottlenecks and I/O latencies.
Lack of Memory:
- If memory (RAM) is insufficient, SQL Server may experience delays in writing changes to disk.
- You should check the query memory consumption and Page Life Expectancy (PLE) value.
Checkpoint Not Running:
- The checkpoint operation may be delayed, so the log file dirty page cannot clear out old ones.
- Checkpoint can be triggered manually.
Lazy Writer Delay:
- The Lazy Writer process may be running slower than usual.
- It may be necessary to control the running speed of Lazy Writer.
Solution Suggestions
You can solve your problem with the following steps OLDEST_PAGE :
Run Checkpoint
First, force the changes to be written to disk by doing a manual CHECKPOINT :
CHECKPOINT;
SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'databaseName';
Check Disk I/O Performance
To see if SQL Server is experiencing a bottleneck in disk operations, run the following query:
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID('databaseName'), NULL);
- Check disk performance.
- You can use faster disks like NVMe SSD.
- Review the disk structure such as RAID or SAN.
io_stall_ms Why?
sys.dm_io_virtual_file_statsLocated in the DMV io_stall_ms, it shows how long SQL Server waits (I/O latency) in milliseconds (ms) when accessing a data or log file.
- High io_stall_ms values indicate that SQL Server is experiencing delays while reading or writing data from disk .
- Low values indicate that I/O operations are running fast .
This value varies depending on the disk type and infrastructure . However, general reference values are as follows:
| Disk Type | Okuma (read_io_stall_ms) | Yazma (write_io_stall_ms) | Toplam (io_stall_ms) |
|---|---|---|---|
| NVMe SSD | 0.1 - 2 ms | 0.1 - 2 ms | 0.1 - 5 ms |
| PCIe SSD | 1 - 5 ms | 1 - 5 ms | 1 - 10 ms |
| SSD | 2 - 8 ms | 2 - 10 ms | 5 - 15 ms |
| SAN Storage (Fast) | 5 - 15 ms | 5 - 20 ms | 10 - 30 ms |
| SAN Storage (Mid-Range) | 10 - 20 ms | 15 - 30 ms | 20 - 50 ms |
| RAID HDD (SAS 15K RPM) | 15 - 30 ms | 20 - 40 ms | 30 - 60 ms |
| RAID HDD (SATA 7200 RPM) | 20 - 50 ms | 30 - 70 ms | 50 - 100 ms |
| Tek Disk HDD (Mechanic) | 50 - 150 ms | 70 - 200 ms | 100 - 300 ms |
Check Page Life Expectancy (PLE) Value
You can check the PLE value to see if you are out of memory :
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page Life Expectancy';
If the Page Life Expectancy (PLE) value is below 300 seconds (5 minutes) , you may be out of memory.
Solution:
- Add more RAM or
- Optimize memory-consuming queries.
Check Lazy Writer Status
The Lazy Writer process may be holding dirty pages in memory for longer than necessary. To check this:
SELECT * FROM sys.dm_os_process_memory;
If lazy_writer_sleep the duration is too long , it may cause unnecessary load on memory.
Solution:
- Identify and optimize memory-consuming queries.
- sp_whoisactive sys.dm_exec_requests Find the most resource consuming processes using or .
Shrink Transaction Log as an Alternative
If CHECKPOINTyour log file is not cleared even after running it, manually shrinking your transaction log file (LDF) may help:
DBCC SHRINKFILE (databaseName_Log, 100); --100 value is in megabytes (MB).
Caution! If your transaction log is still full and there are active transactions, shrinking it will not work.
Summary
- log_reuse_wait_desc indicates why the transaction log could not be cleared.
- The values describe different reasons such as CHECKPOINT, LOG_BACKUP, ACTIVE_TRANSACTION.
- The solution BACKUP LOG can be achieved by operations such as , DBCC OPENTRAN, CHECKPOINT, depending on the problem DBCC SHRINKFILE.