Why does my SQL Server transaction log keep growing, and should I shrink it?

This covers most cases. A database in the FULL recovery model keeps every log record until a log backup copies it out. Full backups don't truncate the log; only log backups do.

The database was probably created from model (which defaults to FULL) and nobody set up log backups. Pick one:

-- You need point-in-time restore: back up the log regularly (e.g. every 15 minutes)
BACKUP LOG YourDb TO DISK = N'X:\Backups\YourDb_log.trn';

-- You don't: switch to SIMPLE, where checkpoints truncate the log
ALTER DATABASE YourDb SET RECOVERY SIMPLE;

Switching to SIMPLE breaks the log backup chain. Only do it if losing point-in-time recovery is acceptable.

Cause #2: a transaction that never ended

A single open transaction pins every log record written after it began, even on a database in SIMPLE recovery:

DBCC OPENTRAN (N'YourDb');

SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       t.transaction_begin_time
FROM sys.dm_tran_active_transactions AS t
JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = t.transaction_id
JOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_id
ORDER BY t.transaction_begin_time;

The typical offender is an SSMS window where someone typed BEGIN TRAN and went to lunch. Also watch for huge single-statement operations: deleting 50 million rows in one DELETE has to be logged as one transaction. Do it in batches instead:

WHILE 1 = 1
BEGIN
    DELETE TOP (50000) FROM dbo.AuditLog WHERE CreatedAt < '2025-01-01';
    IF @@ROWCOUNT = 0 BREAK;
END;

Only then, shrink once

When log_reuse_wait_desc shows NOTHING and the log is still far bigger than it needs to be, shrink it once to a sensible size:

USE YourDb;
DBCC SHRINKFILE (N'YourDb_log', 8192); -- target size in MB

Then set a fixed autogrowth increment (for example 1–4 GB rather than a percentage) so the log grows in a few large steps instead of thousands of tiny ones. Too many VLFs slow down recovery and restores.

Don't shrink on a schedule. A log that fills back up to 30 GB every night needs 30 GB. Shrinking just makes it pay the growth cost again tomorrow, and log growth has to be zero-initialized (SQL Server 2022 can skip that only for growths of 64 MB or less), so writes wait while every extra gigabyte is zeroed out.

Checklist

1. Check log_reuse_wait_desc first.
2. FULL recovery? Schedule log backups, or switch to SIMPLE on purpose.
3. Find and end long-running open transactions; batch large DML.
4. Shrink once, to a size based on your real peak, with a fixed growth increment.

Why is SQL Server using all my server's memory?

It's caching by design, not a leak, but the default limit is 'everything'. How to set max server memory and tell real memory pressure from a full buffer pool.

#configuration#internals#performance