DBAtlas · Database operations atlas

Why the log file grew / why it is not truncating

SQL ServerBackup & Log

Command

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

-- Log space usage for all databases:
SELECT DB_NAME(database_id) AS db, name, type_desc,
       size*8/1024        AS size_mb,
       CAST(FILEPROPERTY(name,'SpaceUsed') AS BIGINT)*8/1024 AS used_mb
FROM   sys.master_files
WHERE  type_desc = 'LOG';

What it does

The log_reuse_wait_desc column tells why the transaction log cannot be truncated (e.g. LOG_BACKUP, ACTIVE_TRANSACTION, REPLICATION, AVAILABILITY_REPLICA).

When to use it

To find the root cause when the log file keeps growing or the log is full (error 9002).

Caution

If LOG_BACKUP is shown, the fix is to take a log backup, not to shrink the file. Query only; no risk.

Open in DBAtlas →

Related