Is a LOG BACKUP allowed on a SIMPLE RECOVERY database

01 September,2016 by Jack Vamvas

I received a question from the backup engineers  configuring a new backup system. They are working with different technology owners to set up the different schedules. As part of this checklist they asked me whether they should trigger a transaction log backup after the FULL BACKUP of databases in the SIMPLE RECOVERY model?

My response to this question included a brief explanation about how SQL Server transaction logs are managed under the SIMPLE RECOVERY model. When a SQL Server database is configured with SIMPLE RECOVERY – a DBA needs to understand that transaction logs will be immediately truncated. The purpose is to remove the inactive virtual log files.  

Database has more than 1000 virtual log files which is excessive – informational message 9017

The transaction log truncation process , occurs normally after any checkpoint. There are exceptions – under certain circumstances.   You'll notice the importance of the CHECKPOINT process when there is a hardware crash failure, if there hasn't been a CHECKPOINT after a large INSERT , the server will be left with a large amount of dirty pages. The processing of these dirty pages will slow down the server recovery. Read more  SQL Server recovery slow after hardware failure

Transaction log backups are only allowed if the database is in the FULL or BULK LOGGED model. If you attempt a BACKUP LOG command you will see an error message like this one:

Msg 4208, Level 16, State 1, Line 1

The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

WARNING: If you require a database recovery point to include changes made recently than the SIMPLE RECOVERY model is not appropriate. You will need to set up a FULL RECOVERY model with LOG BACKUPS satisfying the agreed Service Level Agreements.

Read More on transaction log backups

The transaction log for database is full due to ACTIVE_TRANSACTION

DIRTY_PAGE_POLL and how to avoid it (SQL Server DBA)


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Is a LOG BACKUP allowed on a SIMPLE RECOVERY database


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer