01 September,2016 by Tom Collins
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.
The transaction log for database is full due to ACTIVE_TRANSACTION
DIRTY_PAGE_POLL and how to avoid it (SQL Server DBA)
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |