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?
Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Once the LSN Chain (Log Chain) is broken, you can never do point in time recovery. (there are few exceptions to this, i’ll explain this later)
There are four conditions in which Auto-Truncate Mode gets enabled:
- By switching the recovery model of your database to simple.
- Using BACKUP LOG command with NO_LOG | TRUNCATE_ONLY option. (Note: The BACKUP LOG … WITH NO_LOG | TRUNCATE_ONLY option is no longer available in SQL Server 2008)
- If you have never taken a FULL backup of your database, log backup of that database will also be not available. Your database is in Auto-Truncate Mode until the first FULL backup of the database is taken.
- If you have not taken a FULL\Differential backup after the last time you switched from SIMPLE to either FULL or BULK_LOGGED recovery model.
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:
- You should be owner of the database.
- You should have ALTER ANY DATABASE or VIEW ANY DATABASE server-level permissions.
- You should have CREATE DATABASE permission in the master database.
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
GO
— 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’)
GO
–Now take a full backup of sarab_12 Database
BACKUP DATABASE sarab_12 TO disk = ‘C:\sarab_12.bak’
GO
— 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’)
GO
–Try to truncate the inactive portion of sarab_12 database without taking backup of the same
BACKUP LOG sarab_12 WITH TRUNCATE_ONLY
GO
— Check status of auto-truncate option after truncating log
SELECT db_name(database_id) AS ‘database’, last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = db_id(‘sarab_12’)
GO
–Clean your instance
DROP DATABASE sarab_12
GO
APPLIES TO
MS SQL Server 2000
MS SQL Server 2005
MS SQL Server 2008
Regards
Sarabpreet Anand
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
Sarab,
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.
Sarab,
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.
@Sachin
You made a valid point, but you know the whole article was talking about SIMPLE REcovery Model & not for others.
Anyways Point taken.Innocent
Guys VLF and Log file both have different concept, wen ever checkpoint occure inactive VLF get dumped into log and space is get free for new log.
Pankaj,
Can you please elaborate a bit more what you mean by dumping of VLf to log file ?
Dear Friends,
Please help me , automatically our Sql database transation records were trucated . How to recover that data
Hi,
I am experiencing a weired situation. I have a 5-6 databases.
1. The database full and T-logs have been configured and are executing successfully.
2. Autoshrink is off.
3. Auto-Truncate is off. Checked as per the above article.
Now the situation is that the log space is 10mb. We have a tool that runs to check the %log space usage by running the DBCC SQLPERF(LOGSPACE) command. The log space is 90% full. There is no open transaction in the database, so we have increased the size of the logspace to 50mb to avoid the alerts generated by the tool.
However, after 2-3 hours the size of the logs file goes back to 10mb automatically.
I have checked the T-logs and they don’t have any shrinking otions set.
Can anyone help to whats happening here?
Nice article Sir but i found you are using Truncate_only command in this article and i think this will not work with sql server 2008 .
Please correct me if i am wrong still With No_truncate command works with 2008 but i i dnt think so With No_Log and Truncate only commend work with 2008