Full Backup Does Not Truncate Log
Published: Apr 24, 2015
This is a massive misconception that has been going around for a long while and it needs to be wiped out.

Basically this is the belief that if you have a database in FULL recovery mode and take a full backup, then it truncates the log.

This is NOT true.

I’m forever seeing databases that are in the FULL recovery mode and the server simply has a full backup being taken each night but nothing else. Predictably the server eventually runs out of space and someone complains that SQL Server is broken.

Well, it’s not broken, it’s simply not being run correctly.

Facts are that the only thing which can truncate a log and leave it ready to be reused is a log backup. That’s it.

When you take a full backup all you are doing is generating a backup of the database at that point in time. Therefore effectively all you’re doing is taking a transactionally consistent snapshot of that database. By definition this doesn’t need all the historical log information and therefore it does not back up the log.

As such, if you were to leave a database in the FULL recovery model and only take nightly full backups then your transaction log will grow and grow until one day you find that you have a 100Mb data file attached to a 500Gb log file and can’t understand why.

Once this happens you basically have 2 choices… if you need the log file (in order to allow for point in time recovery) then you will need to take a log backup, place the database into SIMPLE recovery mode, shrink the log file, place back into FULL recovery, and then take another full backup.

Note that when changing a database from SIMPLE to FULL recovery, it remains in SIMPLE mode in the background until the first full backup is taken.

Once you’ve placed the database back into FULL recovery mode, ensure that this time you set up log backups to stop the same situation from repeating.

However, if you do not require the log file to be kept and the database actually doesn’t require FULL recovery mode, then simply place the database into SIMPLE mode and shrink the log file.

The basic commands surrounding this for altering the recovery model and shrinking the log file are as follows:

alter database <myDatabase> set recovery simple
go

dbcc shrinkfile('<myLogFileName>')
go

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron