TRUNCATE Log in SQL Server 2008, 2012, 2014

You have probably seen message like
‘Transaction log of myDatabase is full’.

Prior to version 2008 of SQL Server we will execute statement like

BACKUP LOG MyDatabase WITH TRUNCATE_ONLY

but if you tried to execute this on SQLServer 2008, 2008R2, 2012 and 2014 you will get and error like

?Msg 155, Level 15, State 1, Line 1
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

Assuming that you know that TRUNCATE_ONLY is an dangerous option, not very usable in a live systems and that officialy and recomended way to do it is to change database recovery model from FULL to SIMPLE still you can save a lot of time, space and headaches on test systems, on SQL Server 2008 and later you need to execute

BACKUP LOG MyDatabase
TO DISK = 'nul:' WITH STATS = 10

and magic is done. Your database log is truncated.

Leave a Reply

Your email address will not be published. Required fields are marked *