Backup to clear the transaction log

Question

Post reply

Backup to clear the transaction log

rob.lewis 86087

Right there with Babe

Points: 798
More actions
January 29, 2015 at 6:55 am

#297291

Hi, quick dba question if anyone can help.
Looking at the recovery model on MSDN - was under the belief from it that if you set your database to use the simple recovery model, then when you preform a full back up it would truncate the transaction log file but this doesn't seem to be the case?
Just wondering if there is a way of setting this. We aren't looking to do a point in time recovery for this particular databse.
Cheers
Rob

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

a transaction log is like a box : it's dimensions(size) do not change, only how full or empty it is.

So truncating the log empties the box to make room for more stuff, but doesn't make the box itself smaller.

if you want to shrink the log to recover some disk space, you have to explicitly run the shrink command.

if the log expanded to a given size, and is now empty, something made it expand(big transaction, reindexing), so it's very possible it would expand to the same larger size again automatically in the future.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Full backups do not truncate the transaction log in any recovery model.

In full and bulk-logged recovery a log backup truncates the log.

In simple recovery a checkpoint truncates the log.

The myth that a full backup truncates the log came about because the first thing that a full backup does is run a checkpoint, and in simple recovery model that checkpoint truncates the log. It's not the full backup.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

GilaMonster (1/29/2015)
In simple recovery a checkpoint truncates the log.

And even then there are drivers behind this, it won't necessarily truncate on every checkpoint

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Perry Whittle (1/29/2015)
GilaMonster (1/29/2015)
In simple recovery a checkpoint truncates the log.
And even then there are drivers behind this, it won't necessarily truncate on every checkpoint

It will truncate on checkpoint. It may not clear any of the log, but it will truncate.

Truncate: The marking of 0 or more VLFs as reusable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rob.lewis 86087 Right there with Babe Points: 798 More actions · Answer 5

GilaMonster (1/29/2015)
Full backups do not truncate the transaction log in any recovery model.
In full and bulk-logged recovery a log backup truncates the log.
In simple recovery a checkpoint truncates the log.
The myth that a full backup truncates the log came about because the first thing that a full backup does is run a checkpoint, and in simple recovery model that checkpoint truncates the log. It's not the full backup.

Thanks, so from your response and others am I right in understanding that:

If you have a 20gb transaction log file in simple a recovery model

A full back up is run

A check point is made

The transaction log off the back of that is truncated

The log file still remains at 20gb on disk

If the first thing to run is a 100mb transaction it will start to fill the empty the log file as apposed to increasing it to 20.1gb?

Thanks for the advice 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

rob.lewis 86087 (1/30/2015)
If you have a 20gb transaction log file in simple a recovery model
~~A full back up is run~~
A check point is made
The transaction log off the back of that is truncated
The log file still remains at 20gb on disk
If the first thing to run is a 100mb transaction it will start to fill the empty the log file as apposed to increasing it to 20.1gb?

Yup, though the backup has nothing to do with anything in this case. A checkpoint occurs (runs regularly), the log gets truncated.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rob.lewis 86087 Right there with Babe Points: 798 More actions · Answer 7

GilaMonster (1/30/2015)
rob.lewis 86087 (1/30/2015)
If you have a 20gb transaction log file in simple a recovery model
~~A full back up is run~~
A check point is made
The transaction log off the back of that is truncated
The log file still remains at 20gb on disk
If the first thing to run is a 100mb transaction it will start to fill the empty the log file as apposed to increasing it to 20.1gb?
Yup, though the backup has nothing to do with anything in this case. A checkpoint occurs (runs regularly), the log gets truncated.

Thank you 🙂