SQL server 2008 SQL statement for log shrinking

Sql2008 contraction log is as follows:

The code is as follows:	Copy code
Backup log dbname to disk = 'dbname. Log' Dbcc shrinkfile ('dbname _ log', 1)

However, you must remember to perform the contraction only twice;

Shrink database files (if not compressed, the database files will not be reduced by the Enterprise Manager -- right-click the database you want to compress -- all tasks -- contract database -- contract files
-- Select Log File -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.
-- Select data file -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.

You can also use SQL statements to complete
-- Shrink database
Dbcc shrinkdatabase (database name)

-- Contract the specified data file. 1 is the file number. You can use this statement to query: select * from sysfiles
Dbcc shrinkfile (1)

Of course, we can also directly clear database logs

SQL2008 contraction log

Because SQL2008 optimizes file and log management, the following statements can be run in SQL2005 but have been canceled in SQL2008:
(SQL2005)

The code is as follows:

Copy code

Backup Log DNName with no_log Go Dump transaction DNName with no_log Go USE DNName Dbcc shrinkfile (2) Go -------------------------------------------------------------- (SQL2008 ): Log cleanup in SQL2008 must be performed in simple mode. After the cleanup is completed, the log is called back to full mode. USE [master] GO Alter database DNName set recovery simple with NO_WAIT GO Alter database DNName set recovery simple -- SIMPLE mode GO USE DNName GO Dbcc shrinkfile (n'dnname _ log', 11, TRUNCATEONLY) GO USE [master] GO Alter database DNName set recovery full with NO_WAIT GO Alter database DNName set recovery full -- restore to FULL mode GO

Advantage: It takes a short time to clear logs. A 90 GB log can be cleared in about minutes. After the log is cleared, make a full backup within minutes.
.
Disadvantage: However, it is best not to use this action frequently because its operation will cause system fragmentation. In normal state, logs and DIFF backups can be truncated.
The appropriate environment used by this statement: when the system LOG file is abnormally increased or the backup LOG time is too long, it may affect the production.