SQL Server 2005/2008 methods for compressing database logs

Methods that apply to SQL Server 2005

 1  backup  log  Dnname with   no_log  2  go  3  dump  transaction  Dnname with   no_log  4  go  5  use   Dnname  6  2  )  7  go  

Description: Because SQL Server 2008 has been optimized for file and log management, the above statements can be run in SQL2005 but have been canceled in SQL2008.

Methods that apply to SQL Server 2008

1  Use [Master]2 GO3 ALTER DATABASEDnnameSETRECOVERY Simple withno_wait4 GO5 ALTER DATABASEDnnameSETRECOVERY Simple--Simple Mode6 GO7  UseDnname8 GO9 DBCCShrinkfile (N'Dnname_log', One, Truncateonly)Ten GO One  Use [Master] A GO - ALTER DATABASEDnnameSETRECOVERY Full  withno_wait - GO the ALTER DATABASEDnnameSETRECOVERY Full  --revert to Full mode - GO

Description: [Advantage] This purge log runs short of elapsed time. [Cons] However, this action is best not to be used frequently, as it will result in system fragmentation. Log and diff backups can be truncated in normal state. The appropriate environment for this statement is used when the system's log file abnormally increases or the backup log time is too long to affect production.

Translated from: http://hi.baidu.com/wtnzone/item/53e71b40b46b1e96823ae17b