SQL Server Transaction Log Usage, VLF Count and Log Reuse Wait
Shows log fill level, the reason log reuse is waiting, and VLF count. The first place to look when a log file balloons.
SELECT d.name, d.recovery_model_desc, d.log_reuse_wait_desc,
ls.total_log_size_in_bytes / 1048576 AS log_size_mb,
CAST(ls.used_log_space_in_percent AS DECIMAL(5,1)) AS used_pct,
(SELECT COUNT(*) FROM sys.dm_db_log_info(d.database_id)) AS vlf_count
FROM sys.databases d
CROSS APPLY sys.dm_db_log_space_usage ls -- mevcut DB için; tümü için veritabanı bazında çalıştırın
WHERE d.database_id = DB_ID();A large number of small VLFs (Virtual Log Files) degrades transaction log write throughput and slows database recovery.
- This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
- Prior to executing on production systems under high load, verify the target database version (Min. Version: 2016 SP2).
- Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.
Engineering Triad: Related Runbooks & Incident Response
Which production incidents and emergency drills execute this script as a diagnosis step?
Other SQLSERVER Diagnostic Scripts
View All (100)SQL Server Running Requests and Blocking Chain
Shows active requests, wait types, blocking sessions and the running statement together. The first diagnostic query to run during a slowdown.
Top CPU-Consuming Queries in SQL Server
Lists the top 20 queries by total and average CPU time from the plan cache. Pinpoints the source of CPU pressure at query level.
SQL Server Missing Index Recommendations (Missing Index DMVs)
Ranks the optimizer's missing index suggestions by estimated benefit. Merge them with existing indexes before applying.
SQL Server Index Fragmentation with Rebuild/Reorganize Advice
Measures fragmentation on large indexes and recommends rebuild or reorganize. Lets you plan maintenance windows without wasted work.