Skip to main content

SQL Server Transaction Log Full (error 9002)

SQL Server's transaction log is full and could not grow (error 9002).

SQL Server raises error 9002 when the transaction log needs more space and cannot get it. By default a log file has no size limit other than SQL Server's 2 TB maximum, so this usually means the disk that holds the log file is full. If a maximum size limit has been set on the log file, reaching it causes the same error.

note

Seeing error 701, 802, 8645, or 8657 instead? That is a memory-grant shortage, not a full log. See SQL Server out of memory for the fix, which is different: fewer concurrent writes or more memory, not a log backup.

Resolution​

Check the recovery model and the cause​

Run this in the destination database:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = DB_NAME();

recovery_model_desc tells you which section to follow: FULL or BULK_LOGGED or SIMPLE. log_reuse_wait_desc says why SQL Server cannot reuse log space:

  • LOG_BACKUP: the log is waiting for a transaction log backup. This happens only under FULL or BULK_LOGGED.
  • ACTIVE_TRANSACTION: an open transaction, possibly in another session, is holding the log. Find it with DBCC OPENTRAN, then commit or roll it back before you go on.
  • NOTHING: SQL Server can reuse log space now, so the disk is full or the log file reached its maximum size. Shrink the log file, free space on that disk, or raise the log file's maximum size.
  • Any other value: ask your DBA. A log backup will not fix it.

FULL or BULK_LOGGED recovery​

There are two main resolution options:

  1. Switch the destination database to SIMPLE recovery if you don't need log backups for it. The log then stops growing from run to run, and Speediful's runs get faster.
  2. Otherwise, back up the transaction log now so SQL Server can reuse the space in it, then schedule regular log backups so the log does not fill again. Your DBA chooses the interval from your recovery point objective (RPO). A log backup frees space inside the log file; it does not shrink the file. Use BULK_LOGGED rather than FULL if your backup policy allows it.

Both options let SQL Server reuse the space inside the log file, but neither makes the file smaller. To give the space back to the disk, shrink the log file afterwards. See How recovery models affect Speediful for what each choice costs.

Switch to SIMPLE recovery​

In SQL Server Management Studio (SSMS):

  1. In Object Explorer, right-click the database and choose Properties.
  2. On the Options page, set Recovery model to Simple, then choose OK.

Or in a query window:

ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE;

SQL Server makes the log space reusable at its next checkpoint. To do it right away, run CHECKPOINT; in the database. Then shrink the log file.

Back up the transaction log​

Write the backup to a disk that has free space. The disk that holds the log is usually the one that is full.

In SSMS:

  1. In Object Explorer, right-click the database and choose Tasks > Back Up.
  2. Set Backup type to Transaction Log.
  3. Under Destination, remove any existing entry, choose Add, and enter a file path on a disk with free space. Choose OK.

Or in a query window:

BACKUP LOG [YourDatabase] TO DISK = N'E:\Backups\YourDatabase_log.trn';

Then shrink the log file, and schedule regular log backups with a SQL Server Agent job or a maintenance plan.

To move from FULL to BULK_LOGGED, set Recovery model to Bulk-logged on the database's Options page, or run:

ALTER DATABASE [YourDatabase] SET RECOVERY BULK_LOGGED;

SIMPLE recovery​

Under SIMPLE, SQL Server reuses log space after each checkpoint, so the log does not grow from run to run. It fills when one transaction holds more log space than the disk or the log file's maximum size allows, or when other files fill the disk.

  1. If log_reuse_wait_desc is ACTIVE_TRANSACTION, end that transaction first, as described above.
  2. Run CHECKPOINT; in the database so SQL Server can reuse the log space now.
  3. Shrink the log file to give the space back to the disk.
  4. If the disk is still full, free space on it, or raise the log file's maximum size.

Shrink the log file​

Leave the log large enough for a normal run. A log that has to grow again during every run slows those runs down.

In SSMS:

  1. In Object Explorer, right-click the database and choose Tasks > Shrink > Files.
  2. Set File type to Log.
  3. Choose Reorganize pages before releasing unused space, enter the size to keep in Shrink file to, then choose OK.

Lowering the log file's Size (MB) on the Files page of the database's Properties runs the same shrink.

Or in a query window:

USE [YourDatabase];

-- The log file's logical name and current size
SELECT name, size / 128 AS size_mb
FROM sys.database_files
WHERE type_desc = 'LOG';

-- Shrink it to 1,024 MB
DBCC SHRINKFILE (N'YourDatabase_log', 1024);

The file can end up a little larger than the size you ask for, because SQL Server shrinks a log only in whole internal sections. Under FULL or BULK_LOGGED, the file may barely shrink if the part of the log still in use is at the end of the file. Back up the log again and repeat the shrink.

How recovery models affect Speediful​

Every SQL Server database has a recovery model. It decides how much SQL Server writes to the transaction log and when it can reuse that space. A new database takes its recovery model from the model database, which is FULL unless someone changed it.

SIMPLEBULK_LOGGEDFULL
Speediful's bulk writesMinimally loggedMinimally loggedEvery row logged in full
Log space reusedAfter each checkpointAfter each log backupAfter each log backup
Log file sizeDoes not grow from run to runGrows between log backups, more slowly than FULLGrows by every row written between log backups
Speediful runsFasterFasterSlower
Restore toThe last full or differential backupThe end of any log backupAny point in time covered by log backups

For the databases Speediful writes to, use SIMPLE recovery, or BULK_LOGGED if you need log backups. Under FULL and BULK_LOGGED, the log keeps growing until a log backup runs. Without scheduled log backups it grows with every run until its disk is full.

Wide Salesforce objects make the cost of FULL recovery larger. When a query has more fields than fit in one SOQL query, Speediful splits it into several queries and joins their results in SQL Server. Under FULL recovery every row of that join is logged in full, and the join takes several times longer than under SIMPLE or BULK_LOGGED. Speediful joins the results in batches of records, one transaction per batch, so a log backup that runs during the join frees the log of the batches already joined. Without log backups the log keeps every batch, so it still has to hold the whole join.

The exception is @exists_action = 'append' into a table that already exists. Speediful adds those rows in one transaction, so a failure leaves your table as it was. Under FULL recovery that transaction needs free log space larger than the data it adds, all at once, and a log backup cannot free any of it until the transaction finishes.

Changing the recovery model changes how you can restore the database. SIMPLE has no log backups. Under BULK_LOGGED, a log backup that contains Speediful's writes restores only to its end, not to a point in time inside it. Check with whoever owns backup policy for the database before you switch. Speediful never changes the recovery model for you.

Getting a warning before this happens​

Speediful checks the destination database's recovery model once per run, before its first BULK INSERT. If it is FULL or BULK_LOGGED with no log backup in the last 24 hours, Speediful logs a warning about this risk, so you can see it coming instead of only after error 9002 stops a run. The warning does not fail the run or change anything. If the SQL login Speediful connects as cannot read the backup history in msdb, Speediful skips the check; it never blocks a run.

The warning also appears for a FULL or BULK_LOGGED database that has never had a full backup. Until its first full backup, SQL Server reuses that database's log space after each checkpoint, the way it does under SIMPLE, so the log does not grow from run to run yet. After the first full backup it does, unless you also schedule log backups.

Queries split into several queries​

When a query has more fields than fit in one SOQL query and you append its results to a table that already exists (@exists_action = 'append'), Speediful prints a second warning before it joins the results, if the destination database uses FULL recovery. That join is one transaction (see How recovery models affect Speediful). The warning appears whether or not a recent log backup exists, and it gives the approximate log space the join needs when Speediful can read the size of the results. If you keep FULL recovery, make sure the log can grow by more than that. If it cannot, query into a new table, which Speediful joins in batches, and copy its rows into your table yourself.

If the log fills while Speediful joins the results, nothing is written to the destination table, and an existing destination table keeps its rows. Speediful keeps the downloaded records in the tables it joins them from, and the Results pane names those tables after the error. Free log space and run the query again: the new run replaces them. See Query Results Too Wide for what the tables hold and how to drop them.

When Speediful joins in batches, it builds the result in a table named after the destination table with _vshard_stage, and drops that table if the join fails. If it cannot drop it, for example because the log is still full, it prints a warning that names it:

Speediful could not drop the table dbo.Product2_vshard_stage, which holds part of the combined results. Drop it, or let the next run that queries into Product2 drop it.

Drop the table once log space is free, or run the query again: the next run into the same destination table drops it first.

When this error reaches a stored procedure, Speediful returns exit code 15 and the procedure raises an error whose message begins SQL Server's transaction log is full and could not grow. Find the cause, fix it, then re-run.