0

This one has me stumped and I'm sure there is a simple explanation that I cannot fathom at present.

A few informational pieces:

  • vCentre Database - SIMPLE Recovery Model
  • SQL Server 2008 R2 Standard Edition

We have a FULL Database backup (driven by Ola's scripts) that is failing as a result of a full transaction log. We are running the backup WITH CHECKSUM which I suspect may be the cause, but I don't entirely understand why the FULL Backup would need to perform a write operation against the database log file.

I'm conscious that the FULL Backup will need to read from the transaction log to the LSN of the oldest active transaction for consistency. However, does the FULL BACKUP task write a minute amount into the log file to note that it's happened / happening as well?

Error from the Ola Log:

Date and time: 2017-03-13 21:30:40
Command: BACKUP DATABASE [DB] TO DISK = N'M:\Folder\file.bak' WITH CHECKSUM, NO_COMPRESSION 

Processed 1579192 pages for database 'DB', file 'DB_dat' on file 1.

Processed 855886 pages for database 'DB', file 'DB_log' on file 1.

Msg 9002, Level 17, State 3, Server SERVER\INSTANCE, Line 1

The transaction log for database 'DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Msg 3013, Level 16, State 1, Server SERVER\INSTANCE, Line 1

BACKUP DATABASE is terminating abnormally.

Outcome: Failed

Duration: 00:13:19
  • One of the steps, when a backup is taken, is to execute a checkpoint for that database so minimize recovery - this will write to the log. However, could you append the actual error message to the post? – Sean says Remove Sara Chipps Mar 14 '17 at 17:52
  • Could be the reason then! If the log and associated drive were completely full then a checkpoint write would fail, but after 13 minutes? – Darren Mar 14 '17 at 18:03
  • 1
    It looks as though the log file might have a max size set and there is a current transaction/something holding onto the log while the backup is running. Since the log cannot be re-used until after the backup is completed, this is most likely the cause. What does select log_reuse_wait_desc from sys.databases say for this database? – Sean says Remove Sara Chipps Mar 14 '17 at 19:55
  • Nothing sadly as expanded the disk and log so the problem itself went away. I'm largely trying to understand what could have happened to cause a need to write. – Darren Mar 15 '17 at 10:30
  • In terms of a fix though, you're absolutely correct that the disk holding the log filled to capacity as the Log was configured to autogrow with no limits, so it completely filled the drive. Thanks for your replies. – Darren Mar 15 '17 at 10:32
0

Sean Gallardy provided the information that led to the answer when he commented about the Backup forcing a Checkpoint:

Paul Randall Blog on Checkpionts

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.