Picture the night. It is 2 a.m., the application is throwing error 9002, inserts and updates fail, selects still work, and the log file for the order database is 247 GB on a 250 GB volume. Someone in the group chat has suggested shrinking the log file. Someone else has suggested switching the database to SIMPLE and back.
Both make your night worse. Shrinking a full log file returns nothing, because there is no free space inside it to return. Switching to SIMPLE and back drops the log backup chain, so the point-in-time recovery those log backups were buying you stops existing until the next full or differential backup finishes.
The log is full because something stops SQL Server from reusing the space in it. Find out what before you touch anything.
Step one: ask the database why
SELECT d.name,
d.recovery_model_desc,
d.log_reuse_wait,
d.log_reuse_wait_desc,
CAST(lsu.total_log_size_in_bytes / 1048576.0 AS decimal(19,2)) AS total_log_mb,
CAST(lsu.used_log_space_in_bytes / 1048576.0 AS decimal(19,2)) AS used_log_mb,
CAST(lsu.used_log_space_in_percent AS decimal(5,2)) AS pct_used
FROM sys.databases AS d
JOIN sys.dm_db_log_space_usage AS lsu
ON lsu.database_id = d.database_id;
sys.dm_db_log_space_usage reports the database you are connected to, so run it in the context of the sick one. Microsoft has recommended it over DBCC SQLPERF(LOGSPACE) since SQL Server 2012. Its log_space_in_bytes_since_last_backup column, added in SQL Server 2014, tells you how much log a backup would carry.
One caveat. Microsoft documents log_reuse_wait as the reason "as of the last checkpoint," so a value can be stale. Run CHECKPOINT in the database and read it again before you act on a surprising answer.
What each log_reuse_wait_desc value means and what to do
NOTHING. One or more VLFs are reusable right now. If the file is still full, the log is undersized for the workload, or a growth attempt failed because the volume has no space. Check the growth, is_percent_growth and max_size columns in sys.database_files, and check the disk.
CHECKPOINT. No checkpoint has occurred since the last truncation, or the head of the log has not moved past a VLF. This is routine. Issue CHECKPOINT in the database and look at the VLF layout:
CHECKPOINT;
SELECT file_id, vlf_begin_offset, vlf_size_mb, vlf_sequence_number, vlf_active, vlf_status
FROM sys.dm_db_log_info(DB_ID())
ORDER BY vlf_begin_offset;
sys.dm_db_log_info shipped in SQL Server 2016 SP2. On anything older you are stuck with DBCC LOGINFO.
LOG_BACKUP. The database is in FULL or BULK_LOGGED and a log backup is required before truncation. This is the common one, and the fix is a log backup, not a shrink. Two traps live here. If the database has never had a log backup, Microsoft states you must take two log backups before the engine truncates to the point of the last backup. And a database sitting in FULL because nobody chose FULL, with no log backups scheduled, needs a decision about its recovery model more than it needs a backup.
ACTIVE_BACKUP_OR_RESTORE. A data backup or restore is running and truncation waits for it. Wait, or cancel:
SELECT session_id, command, percent_complete, estimated_completion_time/60000.0 AS est_minutes
FROM sys.dm_exec_requests
WHERE command LIKE 'BACKUP%' OR command LIKE 'RESTORE%';
Microsoft documents estimated_completion_time as "Internal only" and states no unit for it. It reports milliseconds remaining for backup and restore, which is how the column gets read in the field, so the division by 60000.0 above gives minutes on an undocumented behavior. Treat it as a hint, not a number to promise anyone. percent_complete is documented, and the commands it covers include BACKUP DATABASE and RESTORE DATABASE.
ACTIVE_TRANSACTION. A transaction is open, or a transaction is deferred because its rollback is blocked on an unavailable resource. Long transactions hold the log under every recovery model, SIMPLE included. Microsoft adds that a long transaction can fill tempdb's log through internal objects for sorts, hashes and cursors, which even SELECT statements create. The version of this you schedule yourself is a single large DELETE, which logs every row it removes inside one transaction; removing rows in batches so the log can be reused is the fix, and that article covers the logging mechanism and why TRUNCATE TABLE is not the same operation. See the next section.
DATABASE_MIRRORING. Mirroring is paused, or under high-performance mode the mirror has fallen behind the principal. FULL recovery only. Check mirroring_state_desc and mirroring_safety_level_desc in sys.database_mirroring.
REPLICATION. Transactional replication has transactions the Log Reader Agent has not delivered to the distribution database. Check that the agent is running and free of errors first. The value also appears when nobody configured replication: Change Data Capture uses the same log scanning mechanism, and Paul Randal points out that "if CDC is configured but the capture job isn't running, the log_reuse_wait_desc will show as REPLICATION." His advice is to fix the capture job or remove CDC. As a last resort Microsoft documents sp_repltrans followed by sp_repldone to mark pending transactions as distributed. The caution on that procedure's own page: run it by hand and "you can invalidate the order and consistency of delivered transactions." The reset form takes NULL for both LSN parameters:
-- Last resort. This discards pending replicated transactions.
EXEC sp_repltrans; -- inspect what is pending first
EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1;
Microsoft does not say what this costs on the other side. In practice you will be repairing the subscriptions afterward.
DATABASE_SNAPSHOT_CREATION. A snapshot is being created. Routine and brief. Wait.
LOG_SCAN. A log scan is running. Routine and brief. Wait.
AVAILABILITY_REPLICA. A secondary is still applying log records, so the primary cannot overwrite them. This happens in synchronous and asynchronous commit modes both. Run this on the primary and compare truncation_lsn across secondaries to find which one holds the primary back:
SELECT ar.replica_server_name,
drcs.database_name,
drs.synchronization_state_desc,
drs.truncation_lsn,
drs.log_send_queue_size, -- KB not yet sent to the secondary
drs.redo_queue_size, -- KB hardened on the secondary, not yet redone
drs.redo_rate -- KB/sec
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.dm_hadr_database_replica_cluster_states AS drcs
ON drcs.replica_id = drs.replica_id
AND drcs.group_database_id = drs.group_database_id
JOIN sys.availability_replicas AS ar
ON ar.replica_id = drs.replica_id
WHERE drs.is_primary_replica = 0
ORDER BY drs.truncation_lsn ASC;
is_primary_replica arrived in SQL Server 2014.
A growing log_send_queue_size points at the network path. A growing redo_queue_size points at redo on the secondary, where readable-secondary workloads that block the redo thread are a frequent cause.
OLDEST_PAGE. The database uses indirect checkpoints and the oldest dirty page is older than the checkpoint LSN. Indirect checkpoint has been the default for new databases since SQL Server 2016, with a one-minute target recovery time, so you meet this value more than the older documentation suggests. It should be short-lived. If it is not, turn indirect checkpoint off for that database while you investigate:
ALTER DATABASE CurrentDb SET TARGET_RECOVERY_TIME = 0 SECONDS;
XTP_CHECKPOINT. The In-Memory OLTP checkpoint has not run. For memory-optimized tables an automatic checkpoint fires when the log grows past 1.5 GB since the last one, counting disk-based and memory-optimized activity both. Microsoft publishes no remedy for this value, and a manual CHECKPOINT is what practitioners reach for. SQL Server 2014 and later.
SLOG_SCAN. Accelerated Database Recovery is scanning the sLog. SQL Server 2019 and later.
Code 14, and codes 10 through 12. Microsoft marks 10, 11 and 12 as internal use. Code 14 is one value with two spellings in the docs: OTHER_TRANSIENT, "currently not used," on the transaction log page, and "Other" on the sys.databases page. Microsoft's own troubleshooting script treats all four as rare and short-lived. Take a second reading before you chase one.
Finding the transaction that will not end
DBCC OPENTRAN gives you the oldest active transaction and the oldest undistributed replicated transaction, with the session ID:
DBCC OPENTRAN;
The DMV carries the numbers: when the transaction first wrote to this database, how much log it has consumed, and how much it reserved for its own rollback.
SELECT st.session_id,
dt.database_transaction_begin_time,
DATEDIFF(minute, dt.database_transaction_begin_time, GETDATE()) AS minutes_open,
dt.database_transaction_state,
dt.database_transaction_log_record_count,
dt.database_transaction_log_bytes_used / 1048576.0 AS log_used_mb,
dt.database_transaction_log_bytes_reserved/ 1048576.0 AS log_reserved_mb,
r.command,
t.text
FROM sys.dm_tran_database_transactions AS dt
LEFT JOIN sys.dm_tran_session_transactions AS st
ON st.transaction_id = dt.transaction_id
LEFT JOIN sys.dm_exec_requests AS r
ON r.session_id = st.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE dt.database_id = DB_ID()
ORDER BY dt.database_transaction_begin_time;
database_transaction_state 4 means the transaction has written log records, 10 means committed, 11 means rolled back. A transaction sitting at 4 with a begin time forty minutes ago and 60 GB of log used is why your log is full, and no number of log backups releases that space. A log backup frees the inactive part of the log, and that transaction's begin LSN pins everything after it.
An idle session with an open transaction means a client opened one and wandered off. Often the open transaction started as a batch that failed and left @@TRANCOUNT above zero with nobody to commit or roll it back. Find the application, then decide. KILL rolls the work back, the rollback writes more log, and nobody can tell you in advance how long it runs. Jes Schultz Borland gives the reason on the Brent Ozar site: "a rollback is mostly single-threaded," so a load that ran on four cores comes undone on one. Killing a two-hour load at the ninety-minute mark can cost you more than ninety minutes, and the log cannot truncate while it runs. Once you have killed it, ask for progress instead of guessing:
KILL 54 WITH STATUSONLY;
That reports percent complete and estimated seconds remaining, and starts nothing.
Truncation is not shrinking
The two words get used as synonyms and they name different operations.
Truncation marks VLFs reusable. Microsoft: "Log truncation doesn't reduce the size of the physical log file." Shrinking reclaims space from the file system, and it removes only VLFs at the end of the file that hold no part of the logical log. Shrinking a full log file does nothing, because there is no inactive space at the end to give back.
When a log backup clears your 9002, the file stays 247 GB with nearly all of that space reusable inside it. The active portion and the VLF holding the log head stay in use. That is the outcome you wanted.
Why you should not shrink on a schedule
Shrink the log and you have to grow it back, and log growth costs more than data file growth does.
Instant file initialization covers less of the log than people assume. Microsoft's wording: "Historically, transaction log files couldn't be initialized instantaneously. However, starting with SQL Server 2022 (16.x) (all editions) and in Azure SQL Database and Azure SQL Managed Instance, transaction log autogrowth events up to 64 MB can benefit from instant file initialization." Larger growth events get no benefit, and on SQL Server 2019 and below neither does any log growth. For every growth that does not qualify, every byte gets zeroed first, the operation holds latches while it runs, and it shows up as PREEMPTIVE_OS_WRITEFILEGATHER. Growth "can cause the database to pause while the new space is allocated, potentially causing query timeouts."
Then there is the VLF layout. Shrink and regrow in small steps and you get thousands of small VLFs, which Microsoft says slows database startup, log backup and restore, and adds replication, CDC and Always On redo latency. At several hundred thousand VLFs, add mirroring timeouts with errors 1413, 1443 and 1479, and error 701 during restore. SQL Server logs error 9017 when it notices: "Database %ls has more than %d virtual log files which is excessive."
How VLF count is decided
The engine picks VLF sizes from the growth increment. The documented rule:
- SQL Server 2014 and later: if the growth is less than one eighth of the current physical log size, create 1 VLF covering the growth.
- Otherwise use the older rule: growth under 64 MB gives 4 VLFs, 64 MB to 1 GB gives 8 VLFs, over 1 GB gives 16 VLFs.
- Azure SQL Database and SQL Server 2022 and later create 1 VLF when the growth is 64 MB or less.
Work an example. A 100 GB log set to grow 10 percent grows by 10 GB. One eighth of 100 GB is 12.5 GB, so the growth falls under the threshold and the engine creates a single 10 GB VLF. A VLF is freed when no part of the active log lives in it, so all 10 GB has to go inactive before truncation releases any of it. The next growth is 11 GB, then 12.1 GB, each slower to zero than the last. Percentage growth on a large log hurts in both directions at once.
Set growth in megabytes, at or below 1,024 MB for logs, which is Microsoft's best practice. The defaults by version: SQL Server 2016 and later, 64 MB for data and log; 2005 through 2014, 1 MB for data and 10 percent for log; before 2005, 10 percent for both. A database restored from an old instance carries the old settings with it.
The one-time shrink that is legitimate
After a runaway transaction, a missed week of log backups, or a one-off rebuild on a 400 GB table, which is the biggest log writer you schedule on purpose, the log is the wrong size and the VLF layout is a mess. Shrink once, then grow back in controlled steps. Take a fresh backup first. For a 64,000 MB target:
-- 1. Release the space. Repeat after a log backup if the first attempt stalls.
USE CurrentDb;
DBCC SHRINKFILE (CurrentDb_log, 512);
-- 2. Grow back in one pass, in chunks of 8000 MB.
ALTER DATABASE CurrentDb MODIFY FILE (NAME = CurrentDb_log, SIZE = 8000MB);
ALTER DATABASE CurrentDb MODIFY FILE (NAME = CurrentDb_log, SIZE = 16000MB);
ALTER DATABASE CurrentDb MODIFY FILE (NAME = CurrentDb_log, SIZE = 24000MB);
ALTER DATABASE CurrentDb MODIFY FILE (NAME = CurrentDb_log, SIZE = 32000MB);
ALTER DATABASE CurrentDb MODIFY FILE (NAME = CurrentDb_log, SIZE = 40000MB);
ALTER DATABASE CurrentDb MODIFY FILE (NAME = CurrentDb_log, SIZE = 48000MB);
ALTER DATABASE CurrentDb MODIFY FILE (NAME = CurrentDb_log, SIZE = 56000MB);
ALTER DATABASE CurrentDb MODIFY FILE (NAME = CurrentDb_log, SIZE = 64000MB);
-- 3. Fix the growth increment so you never do this again.
ALTER DATABASE CurrentDb MODIFY FILE (NAME = CurrentDb_log, FILEGROWTH = 1024MB);
Each 8000 MB step exceeds 1 GB and exceeds one eighth of the log size going into it, so each produces 16 VLFs of around 500 MB: 128 VLFs in total. Sizing it all in one statement would give you 16 VLFs of about 4 GB, and a 4 GB VLF has to go inactive in full before anything reuses it.
Two warnings before you copy that script. Microsoft's documented remedy grows the file "in one step"; the staged version is Kimberly Tripp's, and it trades one statement for a VLF count you can live with. And the last step clears its one-eighth threshold by 1000 MB, 8000 against 7000, so a ninth step at the same increment sits on the boundary and the VLF count stops behaving. Recalculate before you extend it.
The 8000 MB figure is Tripp's. She uses 8000 rather than 8192 to dodge a bug Paul Randal documented: a request to grow the log by an exact multiple of 4 GB grew the file by about 31 KB and raised no error, and the second attempt worked because the file was no longer an exact multiple away. It hit SQL Server 2005 SP3 and 2008 SP1, and Randal writes, "It's fixed in 2008 R2 onward." The habit costs nothing, so keep it. Her post also carries a January 2015 editor's note about SQL Server 2014. That note points at the VLF creation algorithm change, the one-eighth rule above, and says nothing about this bug. The two facts travel together and belong apart.
Size the log from measurement. Microsoft's list: log consumed during a full backup; log consumed by your largest index maintenance operation; log consumed by your largest batch.
The first item carries a correction Microsoft has not made. Its own bullet says log backups "can't occur until it finishes," stale since SQL Server 2005. Paul Randal: "In SQL Server 2005, the restriction was lifted," and the catch moved: "the log is not cleared when the log backup ends. The clearing of the inactive portion of the log is delayed until the full backup completes." Your log backups run during the full backup and free nothing, so the log accumulates for its whole duration. Measure that.
Adding a second log file is a valid emergency move when the volume is out of space, and Microsoft calls it "a temporary condition to resolve a space issue, not a long-term condition." It buys nothing on performance: log files do not use proportional fill.
Recovery models, in plain terms
SIMPLE. The engine reclaims log space at every checkpoint. No log backups, and none allowed. You lose everything since the last full or differential backup, and log shipping, availability groups, mirroring and point-in-time restore are all unavailable. Correct for a reporting copy you can rebuild from source, wrong for anything holding transactions nobody can re-enter.
FULL. Everything is logged and the log grows until you back it up. Recovery to an arbitrary point in time, and no work lost unless the tail of the log is damaged. FULL with no log backup schedule buys you a log file that grows until the volume fills. Standard and Enterprise default to FULL, Express defaults to SIMPLE, through the model database rather than any edition limit.
BULK_LOGGED. FULL, with minimal logging for BULK INSERT, bcp, SELECT INTO, and index creates and rebuilds. Log backups still required, and they run large because the backup captures the minimally logged pages. Point-in-time recovery is not supported, and if the log is damaged or any bulk operation ran since the last log backup, you redo everything since that backup. Use it around a known bulk load, with a log backup on each side.
What switching to SIMPLE and back costs you
The trick goes: set the database to SIMPLE, checkpoint, shrink the log, set it back to FULL. The file does get smaller, and the bill comes later.
The log backup chain breaks the moment you leave FULL. Microsoft's instructions for coming back: "Immediately after switching to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain," and "The switch to the full or bulk-logged recovery model takes effect only after the first data backup." Until that backup finishes, your log backups restore nothing, and the restore window between the last good log backup and the new full backup is gone. If the volume dies an hour later, you restore to the last log backup taken before the switch.
This trick replaced BACKUP LOG WITH TRUNCATE_ONLY and NO_LOG, which Microsoft discontinued in SQL Server 2008. Read the replacement note published with it: "If you must remove the log backup chain from a database, switch to the simple recovery model." Removing the log backup chain is the operation. That makes it a decision to stop recovering to a point in time, not a shrink technique.
If SIMPLE is the right answer here, set it to SIMPLE, take a full backup, delete the log backup jobs, and write down why. Do not do it at 2 a.m. and set it back at 2:05.
Stopping it from happening again
Back up the log on a schedule that matches your RPO. The most you can lose is the interval between log backups, and fifteen minutes is a common answer for OLTP. That interval also caps how much log accumulates between truncations, so it sets the floor on log size. Someone has to say how many minutes of orders the business will accept losing, and the schedule follows from that number. That is the same conversation as setting the interval from an RPO somebody agreed to, and the answer belongs in the recovery plan, not in a job step nobody reviewed.
Alert on percent used, not on file size. The SQLServer:Databases performance object carries Percent Log Used and Log File(s) Size (KB). Microsoft publishes no threshold; I use 80 percent sustained for more than a few minutes. Watch Log Growths too: a rising count between maintenance windows means the log is undersized or something ran that should not have. When writes are slow rather than blocked, what the waits say when the log is the bottleneck separates a log that is full from a log that cannot keep up.
Sweep log_reuse_wait_desc on a schedule. Anything other than NOTHING, CHECKPOINT or LOG_BACKUP sitting there for minutes deserves a look before it becomes a 9002.
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE log_reuse_wait NOT IN (0, 1, 2)
AND state_desc = 'ONLINE';
Audit recovery models against backup jobs. A database in FULL with no log backup in msdb.dbo.backupset for a day is a 9002 waiting for a busy week:
SELECT d.name,
d.recovery_model_desc,
MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log_backup,
MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full_backup
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b
ON b.database_name = d.name
WHERE d.recovery_model_desc IN ('FULL', 'BULK_LOGGED')
AND d.database_id > 4
GROUP BY d.name, d.recovery_model_desc
HAVING MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) < DATEADD(day, -1, GETDATE())
OR MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) IS NULL;
Leave AUTO_SHRINK off. It defaults to FALSE. It shrinks a file when more than 25 percent sits unused, then the file grows again, and you are back to zeroing log space during business hours.
The order to work in at 2 a.m.
- Read
log_reuse_wait_desc. RunCHECKPOINTand read it again if the answer looks wrong. - Check free space on the log volume. A full volume and a full log are different problems behind the same error.
- LOG_BACKUP: take a log backup, and a second one if the database has never had one.
- ACTIVE_TRANSACTION: run
DBCC OPENTRANand the DMV query above, then decide whether to wait, commit, or kill, knowing the rollback cost. - AVAILABILITY_REPLICA or REPLICATION: go to the replica or the Log Reader Agent. Nothing you do to the log file helps.
- To get writes flowing now, free space on the volume, move the log file, or add a second log file on another volume and remove it once the first can truncate.
- Do not shrink. Do not switch recovery models.
- In the morning: size the log from measurement, do the one-time shrink and staged regrow, set
FILEGROWTHin megabytes, and fix the backup schedule that let this happen.
If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.