DB-유

Why Your SQL Server Transaction Log Keeps Growing

· mssql, transaction log, recovery model, troubleshooting

A 2 GB database with a 180 GB log file. I see this all the time on servers that "have been running fine for years". The disk fills up, the app throws error 9002, someone shrinks the log, and a month later it happens again. Shrinking only hides it. What you want is to find out why the log can't be reused.

SQL Server writes to the log in a circle. Space is reused only once nothing needs the records in it anymore. If something still does, the active part grows until the file has to autogrow.

Ask SQL Server why

The answer is almost always in one column:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
ORDER BY name;

Combined with the actual space in use:

DBCC SQLPERF(LOGSPACE);

The common values of log_reuse_wait_desc are:

  • NOTHING – the log can be truncated; it is just large from a past event.
  • LOG_BACKUP – the database is in FULL or BULK_LOGGED recovery and no log backup has run recently. This is the number one cause.
  • ACTIVE_TRANSACTION – a long-running or forgotten open transaction.
  • REPLICATION – transactional replication or CDC has not read the records yet.
  • AVAILABILITY_REPLICA – a secondary replica in an Availability Group is behind or disconnected.
  • DATABASE_MIRRORING, CHECKPOINT, ACTIVE_BACKUP_OR_RESTORE – self-explanatory and usually transient.

LOG_BACKUP

If the database is in FULL recovery, someone decided (or the model database decided for them) that point-in-time recovery is needed. That requires regular log backups, otherwise the log never truncates. Two valid options:

Option A – keep FULL and back up the log every 15 minutes with an Agent job:

BACKUP LOG [Sales] TO DISK = N'B:\SQLBackup\Sales\Sales_log.trn' WITH COMPRESSION, CHECKSUM, INIT;

Option B – if losing changes since the last full backup is acceptable (development, staging, a warehouse rebuilt nightly), switch to SIMPLE:

ALTER DATABASE [Sales] SET RECOVERY SIMPLE;

What is never valid is BACKUP LOG ... WITH TRUNCATE_ONLY; it was removed in SQL Server 2008 and the equivalent trick of switching to SIMPLE and back breaks the log chain until the next full backup.

Also check the model database. New databases inherit its recovery model, so if model is FULL every new database will need log backups from day one.

ACTIVE_TRANSACTION

Find the oldest open transaction in the database:

USE Sales;
DBCC OPENTRAN;

The output includes the SPID and start time. Look at what that session is doing:

SELECT s.session_id, s.login_name, s.host_name, s.program_name, s.last_request_end_time, t.text
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.session_id = 67;

Typically it is an application that opened a transaction and then waited on user input, or an SSMS window where someone ran BEGIN TRAN and went to lunch. I'll KILL 67 if I have to, but I always tell the owner, because the real fix is in the application. A single open transaction blocks log truncation for the entire database, no matter how small it is.

REPLICATION and AVAILABILITY_REPLICA

For REPLICATION, check that the Log Reader Agent is running in Replication Monitor. If replication was removed sloppily, the database may still be marked as published:

EXEC sp_removedbreplication 'Sales';

For AVAILABILITY_REPLICA, look at sys.dm_hadr_database_replica_states and find the replica with a large log_send_queue_size. A suspended or disconnected secondary holds the primary's log hostage until you resume it or remove it from the group.

Shrink once, then size it properly

Shrink only after the root cause is fixed. I never shrink first; it just grows back. Take a log backup (or run CHECKPOINT in SIMPLE), then:

USE Sales;
DBCC SHRINKFILE (Sales_log, 4096);   -- target size in MB

Then grow it back in one step to a sensible size, so you do not end up with hundreds of tiny virtual log files:

ALTER DATABASE Sales MODIFY FILE (NAME = Sales_log, SIZE = 8192MB, FILEGROWTH = 1024MB);

Never leave autogrowth at the default 10 percent. A fixed increment of 512 MB to 1 GB is predictable and keeps the VLF count reasonable.

A day later

Check that log_reuse_wait_desc shows NOTHING or LOG_BACKUP alternating with NOTHING between log backups, and that DBCC SQLPERF(LOGSPACE) shows a stable file size with a low percentage used. DBCC LOGINFO should return a few dozen rows, not thousands.

Errors you'll probably meet

  • "The transaction log for database 'Sales' is full due to 'LOG_BACKUP'." (error 9002). Take a log backup immediately; if the disk is completely full, add a second log file on another drive temporarily so the backup can run, then remove it.
  • "Cannot shrink log file 2 (Sales_log) because the logical log file located at the end of the file is in use." The active VLF is at the end of the file. Run another log backup and try again; it usually takes two or three rounds.
  • The log grows enormously during index rebuilds even with log backups. That is expected in FULL recovery; the rebuild of a 50 GB index writes about 50 GB of log. Consider BULK_LOGGED for the maintenance window or rebuild large indexes one at a time.
  • DBCC OPENTRAN reports a replicated transaction but there is no replication. This is the leftover-publication case above; sp_removedbreplication clears it.

If you remember one thing, make it log_reuse_wait_desc. It names who's holding the space.