In versions prior to 2008, a simpliest way to reduction the size of log file was execution command BACKUP LOG WITH TRUNCATE_ONLY, and then SHRINKFILE. Because this method was/is a risk – we’re losing whole content of log file without real backup, from version 2008 of MS SQL SERVER, this option was turned off. If we try to use it, we’ll get message:
'TRUNCATE_ONLY' is not a recognized BACKUP option.
So, how to crop log file, if we cannot live with his current size?
We need switch database in simple recovery mode, then execute standard SHRINKFILE procedure and, finally, we should switch back database into full recovery mode:
USE DBNAME ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE WITH NO_WAIT DBCC SHRINKFILE(LOGFILENAME_log, 1) ALTER DATABASE [DBNAME] SET RECOVERY FULL WITH NO_WAIT
We just lost whole data from log file, exactly like in BACKUP LOG WITH TRUNCATED_ONLY. But, to not give him possibility to unchecked grow, we should start cyclic backup of log file, for example, like this:
BACKUP LOG [DBNAME] TO DISK = N'C:\Backup\DBNAME.bak'
If we good define periods between backups, the size of log file will stay small, but we still got possibility to recover data from any point of time.