The transaction log for database ‘MyDB’ is full due to ‘LOG_BACKUP’

Your transaction log is full. Both Microsoft, and about 100 articles and blogs have covered this topic, but let’s take a quick look anyway. Because, you know, it comes up all the time.

Summary:

  1. This error message points to a lack of log backups.
  2. Make sure using sys.databases.
  3. Start backing up the log.
  4. You can shrink the log if necessary.
  5. A note on SIMPLE mode, and why it’s often a terrible idea.

Practical transaction log basics

The transaction log records what happens to your database, including inserts, updates, deletes, and more. (It does not bother to record SELECTs.)

If your database is in FULL or BULK_LOGGED recovery mode, then the transaction log keeps those recorded transactions until you back up the transaction log.

So the golden rule is:

If your database is in FULL or BULK_LOGGED recovery mode, you must take log backups regularly!

Not taking log backups is a common – but not the only – reason why you would get the “trasaction log … full” message. In this case, we got a very specific pointer that this IS the issue:  “…is full due to ‘LOG_BACKUP'”.

But let’s make sure.

Find out why the log won’t truncate

To discover why the log won’t truncate the log file, use this query:

SELECT name
	, state_desc
	, recovery_model_desc
	, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyDB';

This returns the following result set:

name state_desc recovery_model_desc log_reuse_wait_desc
MyDB ONLINE FULL LOG_BACKUP

Our database is online, has full recovery mode, and can’t reuse any portion of the log because it’s waiting on a LOG_BACKUP.

That’s pretty clear.

What to do? Back up the log.

Seriously, implement log backups for your FULL and BULK_LOGGED databases. You can do that with whatever you currently use, or you can download our tool, Minion Backup, for free and set up backups any which way you like.

Shrink the file if necessary

Once backups are under way, you can shrink the log file to a reasonable size. That might be anywhere from 500 Mb to 500 Gb, depending on the size and activity level of your particular database.

This might not be necessary, if the file is already a reasonable size, and/or there’s room on the drive. But I’ve seen a 40 Gb log file for a seldom-used 2 Gb database, and that’s a bit silly.

First, find out how big the log file is (actually, all your log files!) using DBCC SQLPERF(LOGSPACE)

Then you can use DBCC SHRINKFILE() to actually perform the file shrink. Again, don’t go crazy: your log file NEEDS some space to work, so guesstimate (or, if you have log records, find out) how much log space the database generally wants on a normal day, and aim for that.

Related: Shrink a log file…automatically?

Switching to SIMPLE mode is usually a terrible idea

If you google this issue, you’ll find tons of forum advice to just switch to SIMPLE mode and back again. Doing this breaks the log chain, and you lose your ability to replay those log backups. In other words, you’ll only be able to restore up to the last FULL or DIFF backup available.

This is only a good idea under a rather specific range of circumstances. For example,

  • IF this is a database that’s receiving regular full backups, and
  • IF literally nobody cares about recovering any changes since the last full backup, and
  • IF you’re super duper sure about those first two points, and
  • IF this database should really be in SIMPLE mode anyway, then
  • MAYBE it might be okay to switch to SIMPLE mode.

But generally, I’d say, take the log backups.

1 thought on “The transaction log for database ‘MyDB’ is full due to ‘LOG_BACKUP’

  1. Pingback: Why Your Transaction Log Is Full: LOG_BACKUP – Curated SQL

Comments are closed.