In full recovery model, if we take a full backup, the log file is not truncated. It is done when we take a transaction log backup.
Any particular reason of why it is done this way?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Being in the full recovery model means you want the option of restoring to any point in time. That means having a full set of transaction log backups after any database backup.
Imagine you have a db backup from midnight yesterday and log backups from 6, 12 and 18:00. Now you take another db backup. If the log is truncated, you can't restore to 11pm (which would require the previous db backup and all the log backups since).
Only a log backup clears out the inactive portion of the log. The whole point of being in full recovery is to be able to restore a full backup and subsequent log backups so you can restore to a specified point in time. If a full backup cleared out the log (especially if you're not taking log backups), how would you do that?
If all you care about is full backups, and you're okay with only ever being able to recover to the point of the last full backup, then do yourself a favor: switch to simple recovery model. This will make sure the log manages itself more effectively than if you are in full recovery model and don't bother taking log backups (in which case the log will not clear out). If you do care about point-in-time-recovery, then stay in full and schedule log backups at a frequency that makes sense for your business (every 15 minutes, once an hour, etc.).
As an aside, be careful with the word "truncate." Many people associate that with shrinking. Taking a full backup will never do anything with the log file. And taking a log backup clears out the inactive portion, but it does not shrink the file (which many people expect).
In the full recovery model SQL Server does not truncate committed transactions until they have been backed up. It allows creating full, differential and transaction log backups thus making point-in-time recovery possible.
For example: We have full database backup every 12 hours and log backups every 2 hours. Look at the picture below.
Assume we have deleted a the crucial data at 21:00. what shall we do? We can restore our database to 20:59 statement. The transaction log backup that was made at 22:00 allows to roll back to 20:59. In this case you will lose all data from 21:00 to 00:00.
RESTORE DATABASE your_database FROM DISK = 'c:/your_database' WITH NORECOVERY, REPLACE RESTORE LOG your_database FROM DISK = 'c:/your_database_log_14_00.bak' WITH NORECOVERY RESTORE LOG your_database FROM DISK = 'c:/your_database_log_16_00.bak' WITH NORECOVERY RESTORE LOG your_database FROM DISK = 'c:/your_database_log_18_00.bak' WITH NORECOVERY RESTORE LOG your_database FROM DISK = 'c:/your_database_log_20_00.bak' WITH NORECOVERY RESTORE LOG your_database FROM DISK = 'c:/your_database_log_22_00.bak' WITH STOPAT = '2015-11-20 20:59:59.000', RECOVERY
Using log backups you can keep tour data safe and sound. Here are some tips when the full recovery model is better to use:
Use simple recovery model. In simple recovery model when a checkpoint occurs all committed transactions are removed from transaction log automatically.