SQL Server Interview Question #199

A database transaction log is growing rapidly. What should you investigate?

Security, Backup, Recovery, Administration & Real-World Scenarios Senior Advanced

Detailed Explanation

First check the database recovery model and why log truncation is being delayed. In FULL recovery, missing transaction log backups are a common cause, but other causes include long-running transactions, availability/replication dependencies, active backups, and other log reuse waits.

Inspect log_reuse_wait_desc, transaction activity, backup history, and disk capacity. Do not repeatedly shrink the log as a routine solution; shrinking without fixing the cause usually leads to repeated growth and fragmentation of the log file.

After resolving the cause, size the log appropriately for normal workload and configure sensible autogrowth.

Code Example

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

DBCC OPENTRAN ('SalesDb');