Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /home/bluerave/domains/megacyb.org/public_html/wp-content/plugins/qtranslate-x/qtranslate_frontend.php on line 497
2014 – MegaCyborg
30 Jun

Reducing the size of the log file MSSQL 2k8 +

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.