Every now and then questions come up forums asking about how to truncate log file in SQL Server. Truncating a transaction log file means clearing out space in the log file. The transaction log file is automatically truncated in simple recovery model and when log backups are performed. A log backup removes the inactive portion of the logs thus freeing up space in log file. It is not recommended to forcefully truncate transaction log file. It may break the log backup chain. However, when absolutely necessary it can be done as shown below.
The first one is just to backup up the log file for the reason mentioned above. It doesn’t breaks the log backup chain.
BACKUP DATABASE AdventureWorks2014 TO DISK=’c:\backups\Aw2014.bak’
The log backup requires a current full backup else it terminates with error. If the above doesn’t works, it can be done using below query.
USE AdventureWorks2014 GO ALTER DATABASE AdventureWorks2014 SET RECOVERY SIMPLE GO ALTER DATABASE AdventureWorks2014 SET RECOVERY FULL GO
The above query first converts the database to SIMPLE recovery, where the log file is truncated and then back to FULL recovery model. This breaks the log backup chain. Any log backup after this will return the error “Backup log can’t be performed as there is no current database backup”. Once done, take a full back to actually bring database into full recovery model
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
