Hi Friends,

We all knew that, SQL Server Full Recovery Model log truncation occurs due to transaction log backup while under Simple recovery model log truncation occurs due to checkpoints. But here I want to show you the impact of checkpoint on your database while database is in SQL Server Full recovery model. Actually the concept is when you create any new database then by default the recovery model of that database will be full until you made any change on default setting. Here this database will be treated as under simple recovery model until first full backup will not be performed for this database. That means full recovery mode will not be effective for log truncation until you take first full backup. Let me explain you this thing practically:

Now we have one table named as xttest under TESTDB database where recovery model of database is full and inserted 9999 records in that table. Now let me show you the log usage of this database before checkpoint and as well as after the checkpoint occurs.


Here log space used before checkpoint = 52.63283

Here log space used after checkpoint = 16.47296

These values may be different on your systems. Difference between both above values clearly shows that log truncation took place under full recovery model. If we will take first full back on database then log truncation will not be occur due to checkpoint. let me show you this also practically :


Here log space used before checkpoint = 54.17457

Here log space used after checkpoint = 54.37619

This clearly shows that now log truncation not occurs due to checkpoint. Now truncation will only happen after taking transaction log backup:


Here log space used before Transaction log backup = 65.78065

Here log space used after Transaction log backup = 17.26533

Which clearly shows that log backup will take care of log truncation after first full backup for a database under full recovery model.




Prince Rastogi

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook