This is a massive misconception that has been going around for a long while and it needs to be wiped out.
Basically this is the belief that if you have a database in FULL recovery mode and take a full backup, then it truncates the log.
This is NOT true.
I’m forever seeing databases that are in the FULL recovery mode and the server simply has a full backup being taken each night but nothing else. Predictably the server eventually runs out of space and someone complains that SQL Server is broken.
Well, it’s not broken, it’s simply not being run correctly.
Facts are that the only thing which can truncate a log and leave it ready to be reused is a log backup. That’s it.
When you take a full backup all you are doing is generating a backup of the database at that point in time. Therefore effectively all you’re doing is taking a transactionally consistent snapshot of that database. By definition this doesn’t need all the historical log information and therefore it does not back up the log.
As such, if you were to leave a database in the FULL recovery model and only take nightly full backups then your transaction log will grow and grow until one day you find that you have a 100Mb data file attached to a 500Gb log file and can’t understand why.
Once this happens you basically have 2 choices… if you need the log file (in order to allow for point in time recovery) then you will need to take a log backup, place the database into SIMPLE recovery mode, shrink the log file, place back into FULL recovery, and then take another full backup.
Note that when changing a database from SIMPLE to FULL recovery, it remains in SIMPLE mode in the background until the first full backup is taken.
Once you’ve placed the database back into FULL recovery mode, ensure that this time you set up log backups to stop the same situation from repeating.
However, if you do not require the log file to be kept and the database actually doesn’t require FULL recovery mode, then simply place the database into SIMPLE mode and shrink the log file.
The basic commands surrounding this for altering the recovery model and shrinking the log file are as follows:
alter database <myDatabase> set recovery simple