How SQL Server 2012 shrinks the transaction log

sql2008 no longer supports BACKUP LOG database name with NO_LOG statement

BACKUP Log  with No_log

Truncation of transaction log sql2008 prompt error follows
BACKUP LOG database name with NO_LOG
MSG 3032, Level A, state 2, line 1
This statement does not support one or more options (NO_LOG). Consult the documentation for the supported options.

Transaction log Truncation

To prevent the database's transaction log from filling up, routine backups are critical. Under the simple recovery model, the log is automatically truncated when the database is backed up, and in the full recovery model, the log is truncated only after the transaction log has been backed up. However, the truncation process can sometimes occur with delays. For information about identifying and coping with various latency factors, see factors that may delay log truncation.

Attention
The BACKUP LOG with no_log and with truncate_only option has been discontinued. If you must remove the log backup chain from the database when using the full or bulk-logged recovery model, switch to the simple recovery model. For more information, see Considerations for switching from the full or bulk-logged recovery model.

In SQL SERVER 2008, BACKUP log with truncate_only is no longer supported, and to shrink the database log, you first need to truncate the file by setting the database recovery mode to simple.

The statements are as follows:

--BACKUP log TestDB with no_log UseTestDB;GO--Truncate The log by changing the database recovery model to simple.ALTER DATABASETestDBSETRECOVERY Simple;GO--Shrink the Trun cated log file to 1 MB.DBCCShrinkfile (Testdb_log,1); GO--Reset the database recovery model.ALTER DATABASETestDBSETRECOVERY Full; GO

It can also be done through a graphical interface.

By backing up the log first, and then shrinking the log file, the following:

 --perform a full backup firstBackup DatabaseMyDB to Disk='D:\db\mydb.bak'  withInit;--Perform a log backup (default append)Backup LogMyDB to Disk='D:\db\mydb.bak' --Modifying the database recovery modelALTER DATABASEMyDBSETRECOVERY Simple--shrink log to 10MBDBCCShrinkfile (Mydb_log,Ten); --Modifying the database recovery modelALTER DATABASETestDBSETRECOVERY Full;

How SQL Server 2012 shrinks the transaction log