log_reuse_wait_desc, which is the single column that says why a log will not release its space. Shrinking is the last move on this page, not the first.Which Symptom Do You Have?
These incidents look identical from the application side and have nothing in common underneath. Find the line that matches what you are seeing, then take the jump on its band.
| What you are seeing | What it usually means |
|---|---|
| The drive itself is out of space Step 1: which drive is full | |
| Zero bytes free, everything on the instance failing | A volume problem, not a database problem |
| The error names the log Step 2: log file or data file | |
9002, the transaction log for database X is full | The log cannot reuse the space it has |
| The log will not release its space Step 3: log_reuse_wait_desc | |
| A huge log file, plenty free on the drive | Truncation is blocked, or it was sized by accident |
9002 and the log backup job is green | The holdup is not a missing backup |
| Shrink succeeds and nothing gets smaller | The space is still in use, so there is none to give back |
| It is a data file, not the log Step 5: the data file case | |
1105, could not allocate space in filegroup | A data file or filegroup is out of room, or capped |
| Autogrowth failed with space on the drive | A MAXSIZE, or growth set to none |
| It is tempdb Step 6: tempdb plays by different rules | |
1105 or 9002 naming tempdb | Its own incident, with its own rules |
This page routes. Each step is one check and one hand-off to the post that owns the answer, so you are never reading background while an instance is down. The full decode of every error number lives in SQL Server Errors: The Complete Guide.
Step 1: Which Drive Is Actually Full?
Ask SQL Server rather than the server team, because the engine reports the volumes its own files sit on and it reports them as the service account sees them, mount points included.
If the volume genuinely is at zero, free something at the operating system level first, because a log backup written to a full drive fails and leaves you exactly where you started.
Across a whole estate, Get Disk Space on SQL Server is the same check with thresholds and sorting, and Get SQL Server Database File Details tells you which databases are sharing the drive you are about to clear.
SELECT DISTINCT
vs.volume_mount_point AS drive,
CAST(vs.total_bytes / 1073741824.0 AS DECIMAL(10,1)) AS total_gb,
CAST(vs.available_bytes / 1073741824.0 AS DECIMAL(10,1)) AS free_gb,
CAST(vs.available_bytes * 100.0 / vs.total_bytes AS DECIMAL(5,1)) AS free_pct
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
ORDER BY free_pct;On that instance the databases live on D:, which has 205.6 GB free. Any “out of space” error raised by a database on D: is therefore not a disk problem, and Step 2 is where it gets settled.
Step 2: Is It the Log File or a Data File?
Run this in the database named in the error. It gives you four things at once: which file is at 100 percent, how big it is, whether it is allowed to grow any further, and where it sits.
The max_size column is the one people skip and the one that most often explains the incident. A file reading no growth, or a number rather than unlimited, has been capped by somebody, and no amount of free disk will help it.
If the LOG row is the full one, go to Step 3. If it is a ROWS row, go to Step 5. If the database is tempdb, go to Step 6. For the estate-wide versions of this check, use Get Transaction Log Size and Usage for logs and Get Database Free Space Summary or Get Database Sizes and Free Space for data files.
SELECT
f.name AS logical_name,
f.type_desc,
CAST(f.size / 128.0 AS DECIMAL(10,1)) AS size_mb,
CAST(FILEPROPERTY(f.name, 'SpaceUsed') / 128.0 AS DECIMAL(10,1)) AS used_mb,
CAST(FILEPROPERTY(f.name, 'SpaceUsed') * 100.0
/ NULLIF(f.size, 0) AS DECIMAL(5,1)) AS pct_used,
CASE WHEN f.max_size = -1 THEN 'unlimited'
WHEN f.max_size = 0 THEN 'no growth'
ELSE CAST(CAST(f.max_size / 128.0 AS DECIMAL(10,1)) AS VARCHAR(20)) END AS max_size_mb
FROM sys.database_files AS f
ORDER BY f.type DESC, f.file_id;That is the whole incident in two rows. The log is full at 48 MB and capped at 48 MB, the data file is at 29.5 percent with room to grow, and the drive underneath has 205.6 GB free. Nothing about the disk is wrong, so adding space would have fixed nothing.
Step 3: Read log_reuse_wait_desc, the Column That Says Why
A transaction log does not fill up because it is too small. It fills up because something is preventing it from reusing space it has already written, and exactly one column tells you what that something is. Read it before you change anything.
Error 9002 itself names the reason, which is the fastest route of all and the part most people scroll past. The message is not generic. On the database captured in Step 2 the column read LOG_BACKUP against a recovery model of FULL, and the error said the same thing in its own words, is full due to 'LOG_BACKUP' and the holdup lsn is (51:15792:1).
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = N'YourDatabase';Take the value and route on it. Each of these has a post that owns it, and the point of this table is that you do not need to read any of the others.
| Value | What is holding the log, and where next |
|---|---|
LOG_BACKUP | No log backup since the last truncation point. Step 4: free the space |
ACTIVE_TRANSACTION | A session is holding a transaction open, SIMPLE recovery included. Step 4: free the space |
REPLICATION | The Log Reader has not delivered yet, and change data capture counts here. Get Replication Status |
AVAILABILITY_REPLICA | A secondary has not hardened the log, asynchronous mode included. Check Availability Group Latency |
ACTIVE_BACKUP_OR_RESTORE | A full or differential backup is running, so this clears itself. Get Database Backup History |
CHECKPOINT or NOTHING | Nothing is blocking truncation, so a file that is still enormous is a sizing question. Step 5: the data file case |
Those are the six you will actually meet. What log_reuse_wait_desc Is Telling You decodes all thirteen values Microsoft documents, including the rare ones, and SQL Server Transaction Log Full is the full entry for error 9002. If nobody chose FULL recovery on purpose, Recovery Models Explained is the decision sitting behind the whole incident. The column itself is documented on MS Docs under sys.databases.
Step 4: Free the Space the Honest Way
Whatever Step 3 named is the thing to remove. For LOG_BACKUP that is a log backup, and if the log has never been backed up you need two, because the first only establishes the point the second can truncate to. For ACTIVE_TRANSACTION it is the open transaction, found with Get Open Transactions, which also tells you how much log each one is holding so you can see whether killing it is worth the rollback. For REPLICATION and AVAILABILITY_REPLICA the fix is on the other side and nothing you do on this server releases the space.
Here is what a log backup actually does, measured on the same database as Step 2.
Read the size_mb column twice. It did not move. The backup freed 39.8 MB inside the file and the file on disk is still 48 MB, which is correct behaviour and the source of most of the confusion on this topic. The space is now available for reuse, the database is working again, and the drive has exactly as much free space as it did before.
Shrinking is what changes the file on disk, and it is the last move here, not the first. It is worth doing when a file is genuinely oversized after a one-off event and will not need the space back, and it is worth not doing the rest of the time, because Shrinking a Database Causes Fragmentation: Measured, Not Just Stated shows what it costs with real before and after numbers. If you have decided to go ahead, How to Shrink Database Files in Chunks is the way to do it without a single long blocking operation, and MS Docs on managing the size of the transaction log file covers the supported options. Shrinking a log that is 99 percent used will do nothing at all, which is Step 3‘s job to explain.
Step 5: When It Is the Data File, Not the Log
Error 1105 is the data-file version of this incident and it has three distinct causes that Step 2‘s output tells apart. The filegroup is genuinely out of space, or a file is capped by MAXSIZE, or autogrowth is set to none and nobody noticed. Could Not Allocate Space Because the Filegroup Is Full is the full entry and separates the three, and Get Filegroup Space shows free space per filegroup rather than per database, which is the unit 1105 is actually about.
The fix is to raise the cap, grow the file, or add a filegroup on another drive. Before choosing, work out what the file should be, because a database that grew in 1 MB steps for two years has a different problem from one that got a 200 GB load. How to Right-Size SQL Server Database Files is the sizing method, and Instant File Initialization is why a large data file growth can hang the instance while a log growth never benefits from it.
Step 6: The TempDB Case, Which Plays by Different Rules
A full tempdb is not a sizing failure to be fixed by shrinking, it is usually one workload holding internal objects or a version store that will not clear while the transaction that needs it is open. Get TempDB Usage and File Balance splits the space between user objects, internal objects and the version store, which is the division that says who to go and talk to, and it checks the files are the same size, because an uneven set fills the small ones first.
If you are trying to get the space back and nothing is happening, When Shrinking TempDB Just Will Not Shrink covers why and what actually works. Once the incident is over, Get TempDB Configuration is the check that stops it recurring, because file count, equal sizing and growth increments are the settings that decide whether this happens again next month.
Step 7: Stop It Recurring
The incident is over when the space is back. The job is over when it cannot happen the same way again, and that is four checks. Get Autogrowth History reads the default trace and tells you how often files have been growing and by how much, which is the difference between a one-off and a file that has been growing every hour for a year. Get Recovery Model Audit finds the databases sitting in FULL recovery with no log backup schedule, which is the single most common way a log fills up at three in the morning.
A log that grew in small increments leaves a mess behind it. Get VLF Counts measures it, The Hidden Log Performance Problem explains what a high count costs you at recovery and during backups, and the WRITELOG wait type is where the ongoing cost shows up in wait statistics. Finally, Collect Capacity and TempDB Baselines is the collector that turns all of this into a trend, so the next one is a ticket rather than an incident.
Frequently Asked Questions
My transaction log is full but the drive has plenty of space. How?
MAXSIZE rather than the end of the disk. In the Step 2 capture the log was full at 48 MB on a drive with 205.6 GB free. Adding disk would have changed nothing. Read log_reuse_wait_desc first, every time.I took a log backup and the ldf file is still the same size.
Can I just delete the ldf file to get the space back?
Shrink runs, says it succeeded, and nothing gets smaller.
log_reuse_wait_desc is anything other than NOTHING or CHECKPOINT, almost all of the file is still in use and there is nothing to give back, so the command succeeds and achieves nothing. Clear the holdup first using Step 4, confirm the used percentage has dropped, and only then decide whether a shrink is still worth doing.Should I switch the database to SIMPLE recovery so this stops happening?
Related
- SQL Server Transaction Log Full, the full entry for error 9002
- What log_reuse_wait_desc Is Telling You, every value decoded, including the rare ones
- DBA Scripts: Storage and Capacity, the hub for every space and sizing check on this site
- msdb Is Growing: Purge Backup, Job and Mail History Safely, the msdb case, where the history tables are what filled the system drive
- DBA Scripts: Backups and Recovery, the hub for the backup side of Step 4
- Cannot Connect to SQL Server, the same kind of page for the other incident that stops everything
Leave a Reply