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
Improve this question
5
  • 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 Gallardy
    Mar 14, 2017 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, 2017 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 Gallardy
    Mar 14, 2017 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, 2017 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, 2017 at 10:32

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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