SQL Server transaction log full: understanding and fixing error 9002

In short

1. Read the reason. It's in the message (full due to 'LOG_BACKUP') and, for all databases, in Query 1, column wait_reason.
2. Act on the reason. For LOG_BACKUP, take a log backup; for ACTIVE_TRANSACTION, find the open transaction (Query 2); for REPLICATION, get whatever reads the log going again: the replication log reader or the CDC capture job.
3. If you need space right now: grow the log file with SIZE or temporarily add a second log file. Raising only MAXSIZE mostly wasn't enough in the lab once the size limit had been reached.
4. Not first: shrinking, switching to SIMPLE, let alone deleting the log file. None of these removes the reason, and they can cost you recoverability.
5. Afterwards: fix the cause and give the log file a sensible size once.

We reproduced the most common cases on SQL Server 2017 to 2025. Three results surprised even us:

• A full backup doesn't help. It never frees the log, and with the log full it even failed in 6 of 24 attempts. The log backup always worked. More
• Raising only the maximum size mostly failed with 9002 itself. Growing the file with SIZE or adding a second log file always worked. More
• The log can fill up in SIMPLE too, for example because of an open transaction or change data capture. More

What error 9002 tells you

The application receives this message, verbatim:

Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'LogFull' is full due to 'LOG_BACKUP' and the holdup lsn is (95:1920:1).

The part in quotes is the reason. It matches the column log_reuse_wait_desc in sys.databases: why SQL Server can't reuse the space in the log right now. From SQL Server 2022, the message also names the holdup lsn, the point in the log from which nothing can be freed. SQL Server 2017 and 2019 end after the reason. For the three reasons we reproduced, the State value differed: 2 for LOG_BACKUP, 4 for ACTIVE_TRANSACTION, 6 for REPLICATION.

The database stays online. Reads work, every change fails until there is space again. If the log fills up during recovery at startup, however, SQL Server sets the database to RESOURCE_PENDING, according to Microsoft; Query 1 only shows databases that are online. In the lab, it wasn't only the application's inserts that failed, but also a CHECKPOINT (message 5901), some full backups and even ALTER DATABASE when it was only meant to raise the maximum size. More on that below.

Why the log can get stuck: SQL Server writes every change to the log first and only reuses a section once nothing needs it any more. In the FULL recovery model, the next log backup needs it; an open transaction needs it for a possible rollback; replication or change data capture need it until they have read it. As long as one of them is waiting, the file grows. Once it can't grow any more, you get 9002.

Reading the reason

Query 1 shows, for every database on the instance, why the log is stuck, how full it is, when the last log backup ran and whether the file can still grow. It only reads and can be run even while a database is already full.

/* PSG · Transaction log · Query 1: What is holding the log? All databases on the instance   Shows recovery model, wait reason, size, usage, last log backup and whether the log can still grow   (across all log files) per database. Reads system views only. Changes nothing. SQL Server 2017 and later (sys.dm_db_log_stats).   Run it on the primary; on a secondary, sys.dm_db_log_stats returns only some of the values.   Permissions: VIEW SERVER STATE (from SQL Server 2022, VIEW SERVER PERFORMANCE STATE is enough); for the log file   columns also VIEW ANY DEFINITION, otherwise they stay empty. Last log backup from msdb (local history only).   OPTION (MAXDOP 1): a diagnostic query should not occupy parallel workers, least of all on a server under pressure. */SELECT    d.name                                                          AS database_name,    d.recovery_model_desc                                           AS recovery_model,    d.log_reuse_wait_desc                                           AS wait_reason,    CAST(ls.total_log_size_mb AS decimal(12, 1))                    AS log_mb,    CAST(ls.active_log_size_mb AS decimal(12, 1))                   AS used_mb,    CAST(100.0 * ls.active_log_size_mb / NULLIF(ls.total_log_size_mb, 0) AS decimal(5, 1)) AS used_percent,    lb.last_backup                                                       AS last_log_backup,    CAST(ls.log_since_last_log_backup_mb AS decimal(12, 1))         AS since_log_backup_mb,    ls.total_vlf_count                                              AS vlf,    f.files                                                       AS log_files,    CASE WHEN f.files > 1 THEN N'several files' ELSE f.growth END AS growth,    CASE WHEN f.files = 1 THEN f.max_mb END                       AS max_mb,    f.free_mb                                                       AS free_on_volume_mb,    CASE        WHEN ISNULL(f.files, 0) = 0 THEN N'unknown (permission missing)'        WHEN f.any_can = 1 THEN N'yes'        WHEN f.unknown = 1 THEN N'unknown (volume not readable)'        ELSE f.reason    END                                                             AS can_grow,    CASE        WHEN d.log_reuse_wait_desc IN (N'NOTHING', N'CHECKPOINT') AND d.recovery_model_desc <> N'SIMPLE'             AND drs.last_log_backup_lsn IS NULL            THEN N'No full backup yet: log behaves like SIMPLE, no log chain.'        WHEN d.log_reuse_wait_desc = N'LOG_BACKUP' THEN N'Log backup missing or too infrequent.'        WHEN d.log_reuse_wait_desc = N'ACTIVE_TRANSACTION' THEN N'An open transaction is holding the log: query 2.'        WHEN d.log_reuse_wait_desc = N'REPLICATION' THEN N'Replication or CDC is not reading the log.'        WHEN d.log_reuse_wait_desc = N'AVAILABILITY_REPLICA' THEN N'A replica is not catching up.'        WHEN d.log_reuse_wait_desc = N'ACTIVE_BACKUP_OR_RESTORE' THEN N'Backup or restore in progress.'        WHEN d.log_reuse_wait_desc = N'OLDEST_PAGE' THEN N'Usually brief: the checkpoint has not yet reached the oldest page.'        WHEN d.log_reuse_wait_desc IN (N'NOTHING', N'CHECKPOINT')             AND ls.active_log_size_mb > 0.8 * ls.total_log_size_mb THEN N'No backlog, but full: log too small for the load?'        WHEN d.log_reuse_wait_desc IN (N'NOTHING', N'CHECKPOINT') THEN N'No backlog.'        ELSE N'Look up the wait reason in the Microsoft documentation.'    END                                                             AS findingFROM sys.databases AS dJOIN sys.database_recovery_status AS drs ON drs.database_id = d.database_idCROSS APPLY sys.dm_db_log_stats(d.database_id) AS lsOUTER APPLY (    SELECT COUNT(*)                                    AS files,           MAX(CASE WHEN x.can = 1 THEN 1 ELSE 0 END) AS any_can,           MAX(CASE WHEN x.can IS NULL THEN 1 ELSE 0 END) AS unknown,           MIN(x.reason)                                AS reason,           MIN(x.growth)                             AS growth,           MIN(x.max_mb)                               AS max_mb,           MIN(x.free_mb)                              AS free_mb    FROM (SELECT              CASE WHEN mf.growth = 0 THEN 0                   WHEN mf.max_size NOT IN (-1, 268435456) AND mf.size >= mf.max_size THEN 0                   WHEN v.available_bytes IS NULL THEN NULL                   WHEN v.available_bytes < CASE WHEN mf.is_percent_growth = 1 THEN mf.size * 8192.0 * mf.growth / 100                                                 ELSE mf.growth * 8192.0 END THEN 0                   ELSE 1 END                                                      AS can,              CASE WHEN mf.growth = 0 THEN N'no, growth disabled'                   WHEN mf.max_size NOT IN (-1, 268435456) AND mf.size >= mf.max_size THEN N'no, MAXSIZE reached'                   ELSE N'no, volume too full' END                              AS reason,              CASE WHEN mf.growth = 0 THEN N'no growth'                   WHEN mf.is_percent_growth = 1 THEN CAST(mf.growth AS nvarchar(10)) + N' %'                   ELSE CAST(mf.growth / 128 AS nvarchar(10)) + N' MB' END          AS growth,              CASE WHEN mf.max_size IN (-1, 268435456) THEN NULL ELSE mf.max_size / 128 END AS max_mb,              CAST(v.available_bytes / 1048576.0 AS decimal(14, 0))                AS free_mb          FROM sys.master_files AS mf          OUTER APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS v          WHERE mf.database_id = d.database_id AND mf.type = 1) AS x    HAVING COUNT(*) > 0) AS fOUTER APPLY (SELECT MAX(b.backup_finish_date) AS last_backup FROM msdb.dbo.backupset AS b             WHERE b.database_name = d.name AND b.type = 'L' AND b.is_copy_only = 0) AS lbWHERE d.state = 0                                                   -- ONLINE onlyORDER BY used_percent DESCOPTION (MAXDOP 1);

On SQL Server 2022, Query 1 returned this row for the database from the first case, shown vertically:

database_name        LogFull
recovery_model       FULL
wait_reason          LOG_BACKUP
log_mb               64.0
used_mb              62.6
used_percent         97.8
last_log_backup      NULL
since_log_backup_mb  62.6
vlf                  32
log_files            1
growth               8 MB
max_mb               64
can_grow             no, MAXSIZE reached
finding              Log backup missing or too infrequent.

Query 1 ran without errors on SQL Server 2017, 2019, 2022 and 2025, including while databases were full. The lab limit of 64 MB makes the case happen within seconds.

How to read the key columns:

• wait_reason is the reason from the error message. NOTHING means nothing is holding the log.
• used_percent shows how full the log is right now. A large log with little usage is fine; a small log at 95% is not.
• last_log_backup comes from the backup history in msdb, excluding COPY_ONLY backups, because those don't free the log. It's this instance's history: if you back up on a secondary in an availability group, the log backup is recorded there. We deliberately don't read it from sys.dm_db_log_stats: its column log_backup_time showed the time of a full backup in the lab, although no log backup had ever run.
• can_grow says, across all log files of the database, whether any of them still has room: growth disabled, MAXSIZE reached, or the volume too full for the next step.
• finding sums up what to do first. A database in FULL without a first full backup is flagged separately (see below).

The common reasons and what helps

LOG_BACKUP: the log backup is missing

In the FULL or BULK_LOGGED recovery model, SQL Server only frees a section of the log once a log backup has backed it up. If no log backup runs, the log grows until it can't. This is the most common case. In the lab we reproduced it step by step, with a log starting at 8 MB, growing by 8 MB, 64 MB at most:

Three things from this matter in an emergency:

• Before the first full backup, nothing holds the log back. A newly created database, or one switched to FULL, has no log chain until it has a full backup. Its log is reused as in SIMPLE. Only the first full backup starts the requirement for log backups; after switching from SIMPLE to FULL, a differential backup does too. Anyone who sets FULL and forgets the log backup therefore often only notices days later. Query 1 reports this state as "No full backup yet".
• A full backup doesn't help. It doesn't free the log, and with the log full it even failed with 9002 in six of 24 attempts (message 3013, BACKUP DATABASE is terminating abnormally.). We saw no version difference we could substantiate, with six attempts per version, and the space left in the log was similar each time, 116 to 136 KB. Why it sometimes goes through and sometimes not is an open question.
• The log backup works even with the log full. It needs hardly any log space itself and frees the log afterwards. A single one was enough in the lab, although the log had never been backed up before. Microsoft's page on 9002 recommends two log backups in that case; it doesn't hurt, but it wasn't needed here. The file stays the size it is. Free space inside the log is the goal, not a smaller file.

-- Frees space in the log when the wait reason is LOG_BACKUP. Adjust path and name.BACKUP LOG [YourDatabase] TO DISK = N'X:\Backup\YourDatabase_log.trn' WITH CHECKSUM;

Write the backup to a target with space, not to the volume where the log is filling up. And never to NUL: BACKUP LOG … TO DISK = 'NUL' does free the log, but throws the backup away and breaks the log chain.

After that, the real work is the cause: why didn't a log backup run? A missing job, a failed job, a backup target that filled up, or a database set to FULL without anyone setting up log backups. How log backups fit into a backup strategy is covered in SQL Server Backup and Restore: The Basics.

ACTIVE_TRANSACTION: a transaction is still open

As long as a transaction is open, SQL Server may not free the part of the log from its first record onwards, because it needs it for a possible rollback. This applies in every recovery model, SIMPLE included. In the lab, one session began a transaction, changed a row and then did nothing, like an application that forgets its COMMIT. A second session kept writing.

After 53 inserts, 9002 with ACTIVE_TRANSACTION appeared on all four versions. A CHECKPOINT didn't help; it failed itself with 9002 and message 5901 (One or more recovery units belonging to database 'HoldTx' failed to generate a checkpoint.).

The transaction itself had used hardly any log. But it held the beginning in place, and everything after it had to stay. A single forgotten transaction is enough to fill a log of any size; it just takes longer. With Accelerated Database Recovery, which can be enabled per database from SQL Server 2019, a long transaction no longer holds the log in this way, according to Microsoft; in the lab it was off.

Query 2 shows which session it is:

/* PSG · Transaction log · Query 2: Who is holding the log? Oldest open transactions   Shows the oldest transactions that have already written to the log, with session, program, duration and last command.   Reads system views only. Changes nothing and kills no session. SQL Server 2016 SP2 and later (sys.dm_exec_input_buffer).   Permissions: VIEW SERVER STATE; from SQL Server 2022, VIEW SERVER PERFORMANCE STATE is enough.   Caution: the last command can contain confidential values.   OPTION (MAXDOP 1): a diagnostic query should not occupy parallel workers. */SELECT TOP (20)    DB_NAME(dt.database_id)                                         AS database_name,    st.session_id                                                   AS session_id,    s.status                                                        AS session_status,    s.login_name                                                    AS login_name,    s.host_name                                                     AS host_name,    s.program_name                                                  AS program_name,    dt.database_transaction_begin_time                              AS first_log_record,    DATEDIFF(second, dt.database_transaction_begin_time, SYSDATETIME()) AS open_s,    CAST(dt.database_transaction_log_bytes_used / 1048576.0 AS decimal(12, 1))     AS log_used_mb,    CAST(dt.database_transaction_log_bytes_reserved / 1048576.0 AS decimal(12, 1)) AS log_reserved_mb,    r.command                                                       AS running_now,    REPLACE(REPLACE(LEFT(ib.event_info, 200), CHAR(13), N' '), CHAR(10), N' ') AS last_commandFROM sys.dm_tran_database_transactions AS dtJOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = dt.transaction_idJOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_idLEFT JOIN sys.dm_exec_requests AS r ON r.session_id = st.session_idOUTER APPLY sys.dm_exec_input_buffer(st.session_id, NULL) AS ibWHERE dt.database_transaction_begin_time IS NOT NULL                -- has already written to the log  AND st.session_id <> @@SPIDORDER BY dt.database_transaction_begin_timeOPTION (MAXDOP 1);

In the lab it found the session immediately, SQL Server 2022, shortened:

database_name  session_id  session_status  program_name  open_s  log_used_mb  last_command
HoldTx         54          sleeping        SQLCMD        9       .0           BEGIN TRAN; UPDATE dbo.Orders SET CustomerId = CustomerId + 1 WHERE OrderId = 1;

sleeping with an open transaction means: the session is waiting for its application, not for SQL Server. Host, program and last command show where to ask. The last command is the batch the session sent last, not necessarily the one that started the transaction. Transactions without a session, such as orphaned distributed transactions, don't show up in Query 2; DBCC OPENTRAN in the affected database helps there.

How to end the transaction: ideally through the application, with COMMIT or ROLLBACK. Find out first what the transaction is doing: a forgotten COMMIT, a load process, index maintenance. Only if the application can't do it, use KILL <session>. KILL rolls the transaction back, and all its changes are lost. For a long transaction, the rollback itself can take a long time; KILL <session> WITH STATUSONLY shows the progress. Microsoft advises using KILL with extreme caution. In the lab, the log was free again after KILL and a checkpoint, 0.1 to 0.2 MB used; on SQL Server 2019, log_reuse_wait_desc still showed ACTIVE_TRANSACTION until the next checkpoint. In FULL, only the next log backup frees the space.

REPLICATION: replication or CDC isn't reading the log

Transactional replication and change data capture (CDC) read their changes from the log. Until they have read them, that section stays in use. If the log reader or the CDC capture job isn't running, the log grows, regardless of the recovery model, SIMPLE included.

That seems odd at first, since SIMPLE is supposed to keep the log small. But it's by design: SIMPLE only spares you the log backup. For CDC, Microsoft states explicitly that even in SIMPLE the point up to which the log is freed only moves on once the capture process has read all changes, and that a CHECKPOINT frees nothing until then. For replication, the same follows from Microsoft's description of the log architecture: undelivered replicated transactions hold the log in any recovery model. Only the page on error 9002 notes "Full recovery model only" for the reason REPLICATION, which doesn't match the other pages. In the lab we reproduced CDC, not transactional replication.

In the lab we enabled CDC for a table without the SQL Server Agent running. The capture job could never start, just as after a stopped Agent or a disabled job.

After 51 inserts, 9002 with REPLICATION appeared; the database was in SIMPLE. We then started the capture run by hand (sys.sp_cdc_scan), the way the restarted job would. With the log full it failed with message 18805 (The Log-Scan Process failed to construct a replicated command …), on 2025 directly with 9002.

The order mattered here. The capture run writes the changes into the CDC tables and probably needs log space for that itself. Only once the log had plenty of room to grow, via a second log file without a size limit, did it catch up and free the log. With 64 MB of extra space it failed again with 18805, although the log wasn't full; we didn't establish the exact cause. In production the path is the same: first make room, then restart the Agent or the capture or log reader job and wait until it has caught up.

Disabling CDC with the log full (sys.sp_cdc_disable_db) also freed the log, but reported error 22831 on 2017 to 2022. Check is_cdc_enabled in sys.databases afterwards. Above all, disabling removes change tables, jobs and metadata, including captured changes nobody has collected yet. It's a last resort, only after talking to whoever uses that data.

Other reasons

The descriptions follow Microsoft's documentation on error 9002. In the lab we reproduced LOG_BACKUP, ACTIVE_TRANSACTION and REPLICATION.

Act now without causing damage

When the application is down, every minute counts. This order has proven itself:

1. Run Query 1 and note the wait reason of the affected database. Save the output.
2. Act on the reason: LOG_BACKUP → log backup. ACTIVE_TRANSACTION → Query 2, have the transaction ended. REPLICATION → log reader or CDC job. AVAILABILITY_REPLICA → replica.
3. If that isn't possible right away and the volume has space: grow the log file with SIZE. The application can keep working while you fix the reason. SQL Server writes zeros over all new log space; a large step can take minutes in an emergency. Pick a step you're sure will be enough, and set MAXSIZE deliberately afterwards. When the new size exceeded the old maximum, SQL Server silently raised MAXSIZE to exactly that size in the lab, so the file couldn't grow any further.

-- Buys time, but doesn't remove the reason. Adjust size and name.ALTER DATABASE [YourDatabase] MODIFY FILE (NAME = N'YourDatabase_log', SIZE = 4096MB);-- Then set the growth limit deliberately:ALTER DATABASE [YourDatabase] MODIFY FILE (NAME = N'YourDatabase_log', MAXSIZE = 16384MB);

1. If the volume is full: temporarily add a second log file on another volume and remove it again once the reason is fixed. Microsoft explicitly describes multiple log files as a temporary state. In an availability group, with log shipping or mirroring, the path must exist on all replicas and have space there, otherwise the database on the secondary is suspended. There, SIZE on the existing file is the safer route, as long as its volume has space.

-- Temporary. Adjust path, sizes and name; with a maximum size so this volume doesn't fill up too.ALTER DATABASE [YourDatabase] ADD LOG FILE (NAME = N'YourDatabase_log2',    FILENAME = N'Y:\Log\YourDatabase_log2.ldf', SIZE = 2048MB, FILEGROWTH = 512MB, MAXSIZE = 8192MB);

You can only remove the second file again once no active part of the log is in it. In the lab, the first attempt failed on all versions with message 5042 (The file 'N3a_log2' cannot be removed because it is not empty.). After more activity, another log backup and a checkpoint it worked. The file was still listed in sys.master_files with size 0 afterwards; according to Microsoft, the entry disappears with the next log backup.

-- Only remove once the second file has no active VLFs (active = 0), otherwise message 5042.SELECT file_id, COUNT(*) AS vlfs, SUM(CAST(vlf_active AS int)) AS activeFROM sys.dm_db_log_info(DB_ID()) GROUP BY file_id;ALTER DATABASE [YourDatabase] REMOVE FILE [YourDatabase_log2];

In the lab we compared three routes with the log 100% full and the maximum size of 64 MB reached, for LOG_BACKUP, ACTIVE_TRANSACTION and REPLICATION on all four versions. Growing with SIZE and a second log file worked in 12 of 12 cases, after which the application could write again. Raising only MAXSIZE failed itself with 9002 in 10 of 12 cases, probably because this change also needs a few bytes of log. With a full volume instead of a reached maximum size, MAXSIZE doesn't help anyway; we didn't reproduce that case.

1. Run Query 1 again: is the wait reason back to NOTHING, and is used_percent dropping?

What not to do first:

• Shrinking. As long as something is holding the log, there's nothing to release. In the lab, DBCC SHRINKFILE with the log full either ran without any effect or failed itself with 9002.
• Switching to SIMPLE. With the log full, the switch itself or the checkpoint it needs can fail with 9002; in the lab a CHECKPOINT failed with the log full. Above all, SIMPLE ends the log chain: you can then only restore up to the last log backup. After switching back to FULL you need a full or differential backup right away and log backups again, otherwise the database silently stays unprotected. In an availability group, with log shipping or mirroring, SIMPLE isn't possible. For ACTIVE_TRANSACTION and REPLICATION it doesn't help at all.
• A log backup to NUL. It frees the log, but throws the backup away and breaks the log chain just the same.
• Deleting the log file or reattaching the database without a log. That can leave the database inconsistent. There's no 9002 case in which that is the right first step.

Bringing the file back to a sensible size

After the emergency, the log file is often much larger than needed. Shrinking it once is fine if the reason has been fixed and the file then gets a planned size. In the lab:

After the log backup and 20 more inserts came a second log backup and DBCC SHRINKFILE to 8 MB. All versions returned the same message:

Cannot shrink log file 2 (LogFull_log) because the logical log file located at the end of the file is in use.

Still, the file shrank from 64 to 26 MB on 2019 and to 30 MB on 2022 and 2025. On 2017 it stayed at 64 MB. Shrinking only goes down to the last section in use. If that is at the end of the file, only the next log backup and a second attempt help. Shrink in steps to the planned size, not to the minimum, otherwise the file has to grow again straight away in production.

Plan the size so that the largest regular transaction and the load between two log backups fit, with headroom. Growth in fixed MB steps rather than percent keeps the number of virtual log files (VLFs) in check. Many small growth steps create many VLFs, and many VLFs slow down startup, recovery and replication. How many are created depends, according to Microsoft, on the size of the step relative to the file, with a changed rule from SQL Server 2022. In the lab, each time writing a little over 64 MB:

In this setup there was no difference between versions. That's surprising: Microsoft describes a changed rule from SQL Server 2022, under which growth of up to 64 MB creates only one VLF. Under that rule there should have been fewer on 2022 and 2025; we counted the same number on all versions, matching the older rule. Either way, the table shows what matters: small steps create many times more VLFs, here eight times as many for about half the size.

Keeping it from happening again

• Log backups at the pace of your recovery objective. If you can tolerate at most 15 minutes of data loss, back up the log at least every 15 minutes. That also keeps the log small.
• An alert on Query 1. A wait reason that stays other than NOTHING for more than a few minutes, usage above 80% or can_grow = no are early warnings, long before 9002.
• Monitor long transactions. Query 2 shows which sessions have been holding a transaction open for minutes.
• Consider maximum size and volume together. A MAXSIZE protects the volume, but leads straight to 9002 if it's too tight.
• Check databases in FULL: does each one have a log backup? Query 1 flags FULL without a first full backup separately.

These are exactly the points a weekday system check covers. With the PSG DBA Pool we check backups, jobs, error log and disk space, among other things, and a SQL Server Health Check looks at backup chains and recovery as a whole.

Permissions for the two queries

Queries 1 and 2 read instance-wide views. Without the permission, a login got message 300 in the lab, verbatim:

Msg 300, Level 14, State 1, Line 7
VIEW SERVER STATE permission was denied on object 'server', database 'master'.

That was the wording on SQL Server 2017 and 2019. On 2022 and 2025 the same message names VIEW SERVER PERFORMANCE STATE; that narrower permission alone was enough there. With VIEW SERVER STATE, Query 1 returned all databases, but the growth and volume columns stayed empty and can_grow showed "unknown (permission missing)". The reason: sys.master_files only shows files with VIEW ANY DEFINITION (or CREATE DATABASE, ALTER ANY DATABASE). Query 2 doesn't need VIEW ANY DEFINITION; from 2022, VIEW SERVER PERFORMANCE STATE alone was enough for it. For a monitoring account that means: VIEW SERVER STATE, or from 2022 VIEW SERVER PERFORMANCE STATE, plus VIEW ANY DEFINITION. Query 1 reads the last log backup from msdb.dbo.backupset; in the lab the same account could read it without further permissions.

Frequently asked questions

Why doesn't the .ldf file get smaller after the log backup? Because the log backup frees space inside the log, not on the volume. The file keeps its size and is reused on the next pass. It only gets smaller through deliberate shrinking, and that's only needed if it's far too large after an exceptional event.

Does a full backup help against a full log? No. It doesn't free the log. In the lab it even failed with 9002 in 6 of 24 attempts with the log full. The log backup is the right step; it worked in all 24 attempts.

May I switch the database to SIMPLE? If this database doesn't need point-in-time recovery, SIMPLE is a deliberate and legitimate choice. As an emergency measure against 9002, it costs you the log chain. Decide based on your recovery objective, not on the error.

Why does the log grow in SIMPLE? Because an open transaction, replication, CDC or a very large single transaction hold the space there too. SIMPLE only spares you the log backup.

How large should the log be? Large enough for the largest regular transaction, such as index maintenance or a load process, and for the load between two log backups. There's no fixed rule of thumb based on database size.

And if TempDB is full? That's a separate case with its own causes, such as the version store. Version Store and Transaction Log explains it.

When it's urgent

If the application is down because the log is full, our urgent help is there for you. For a quick assessment, it helps to have:

• the output of Query 1, and for ACTIVE_TRANSACTION also of Query 2,
• the exact error message with the reason in quotes,
• whether and when log backups run and where they write to.

Sources

• Microsoft: Troubleshoot a full transaction log (SQL Server Error 9002)
• Microsoft: SQL Server transaction log architecture and management guide
• Microsoft: sys.dm_db_log_stats
• Microsoft: About change data capture
• Microsoft: sys.master_files

Read on: SQL Server Backup and Restore: The Basics · Version Store and Transaction Log · Setting up SQL Server Query Store · Urgent help