Auto-Truncate Log in Full Recovery Model
FULL Recovery model: This means that all database changes are fully logged and ideally the log records should stay in the log file until the log records are safely stored away\backed up in a Transaction Log backup. As per MSDN: If a DB is in Full Recovery Model, then No work is lost due to a lost or damaged data file & we Can recover to an arbitrary point in time (for example, prior to application or user error) provided we have all backups in place.
But unfortunately this is not completely correct. Your database can be in Auto-Truncate Mode while the recovery model is set to FULL.
Now what is Auto-Truncate Mode: This means your DB is still working as if it is in SIMPLE Recovery Model. or in other words, the log file will be truncated every time a CHECKPOINT is run against the database. CHECKPOINT happens at regular intervals. Log Truncation means that inactive parts of the log file will be overwritten if the log space is needed. Log Truncation does not physically shrink the log file, but it can keep it from physically growing.
In this article i will explain you the different conditions which changes this behaviour & let you know how to avoid such pitfalls and make your database fully recoverable. This all is internally related to LSN.
Now What is LSN?
There are four conditions in which Auto-Truncate Mode gets enabled:
You should also be aware that taking a log backup will truncate the log, but taking a FULL database backup will not truncate the log. (I received many queries from DBA’s asking this specific question)
How to check whether your database is in Auto-Truncate Mode or not?
You can query a system view called sys.database_recovery_status. If the value of last_log_backup_lsn column of this view is NULL it means the database is not maintaining a sequence of log backups and it is in Auto-Truncate Mode:
Note: To see the row for a database other than master or tempdb, you should have one of the following permission:
SELECT @@servername,db_name(database_id) as 'database', last_log_backup_lsn FROM sys.database_recovery_status(nolock)
How to resolve this?
To resolve this you need to either take a FUll or Differential backups to bridge a gap & recreate a new LSN Chain.
To replicate the same and test following is the step by step script.
CREATE DATABASE sarab_12
-- Check the status of auto-truncate option after first creating Database
SELECT db_name(database_id) as 'database', last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = db_id('sarab_12')
--Now take a full backup of sarab_12 Database
BACKUP DATABASE sarab_12 TO disk = 'C:\sarab_12.bak'
-- Check status of auto-truncate option after taking full backing up of sarab_12 Database
SELECT db_name(database_id) AS 'database', last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = db_id('sarab_12')
--Try to truncate the inactive portion of sarab_12 database without taking backup of the same
BACKUP LOG sarab_12 WITH TRUNCATE_ONLY
-- Check status of auto-truncate option after truncating log
--Clean your instance
DROP DATABASE sarab_12
APPLIES TOMS SQL Server 2000
MS SQL Server 2005MS SQL Server 2008
Sarabpreet is SQLServer MVP, DBA, Speaker, Trainer, Blogger and Community Lead. You can find him speaking at a local UG Event or a SQL Webcast. He has 8+ years of Experience and worked with Industry Leaders like Wipro, HP and HCL. He has many SQL Certifications under his belt. His core competency lies in administration of SQL Server. Always ready to help, online \offline. His life’s mantra is “Knowledge Increases by sharing so, Pass it on”. To know about his speaking engagements visit: here...
Follow Sarab on
Once again great article but one thing needs to be corrected here.There is a myth going around in the SQL Server circles that "Checkpoint causes Log Truncation".This is not at all true.It is the log backup that causes log truncation.I will show you how...
A log file consists of numerous VLF's which gets filled up in a circular fashion which are in state of "active" or "inactive".When the log records are recorded in VLF's those VLF's become "active".The process of changing the state of the VLF's from "active" to "inactive"(which is done by clearing the log records from "active" VLF's) so that those "active" VLF's can be reused is called as Log Truncation.
Now when a checkpoint occurs the VLF's are NOT directly marked as inactive for use instead it is marked as "reusable"(log records are marked as invalid).The log records of the "reusable" VLF's needs to be captured in the log backup then only will those "reusable" VLF's be used for new set of log records and can truly be called as "inactive" VLF's.
The reason all this is done ??? To maintain log chains.Because if checkpoint directly goes and changes the state from "active" to "inactive"(i.e clearing the log records from "active" VLF's so that they can be used for next set of log records) the next log backup wont be having the log records for those changed VLF's and the backup log chain would break.So to do that we need to have all the log records from "active" as well as "inactive"(if they were set to "reusable") VLF's.
This can only be done using log backup's.So in short always remember
CHECKPOINT <> LOG TRUNCATION...
LOGBACKUPS = LOG TRUNCATION...
Very True Sachin but even my statement says the same, read this:
Now what is Auto-Truncate Mode: This means your DB is still working
as if it is in SIMPLE Recovery Model.
or in other words, the log file will be truncated every time a
CHECKPOINT is run against the database. CHECKPOINT happens at regular intervals.
Log Truncation means that inactive parts of the log file will be overwritten
if the log space is needed.
& I was talking about SIMPLE Recovery Model where Log Backup is also not allowed.
Anyways, thanks for reviewing the articles.
I was directing my comments for this particular statements made by you.
"Your database can be in Auto-Truncate Mode while the recovery model is set to FULL or in other words, the log file will be truncated every time a CHECKPOINT is run against the database...This means your DB is still working as if it is in SIMPLE Recovery Model...the log file will be truncated every time a CHECKPOINT is run against the database."
The above is simply not true.If you have worked on SQL 7.0 there was a option "Truncate log on CheckPoint" which did exactly what you claimed since Simple Recovery Model was only introduced from SQL 2000.
What you claim is totally valid ONLY for the checkpoints which occurs in SIMPLE Recovery Model and not all the checkpoints.
You made a valid point, but you know the whole article was talking about SIMPLE REcovery Model & not for others.
Anyways Point taken.
Can you please elaborate a bit more what you mean by dumping of VLf to log file ?
Leave a comment