Summary of methods for shrinking and clearing large log files in SQL Server 2008

Because SQL2008 optimizes file and log management, the following commands can be run in SQL2000 and 2005, but SQL2008 does not support clearing logs by no_log.

1. Clear logs

The code is as follows:	Copy code
Dump transaction database name WITH NO_LOG

2. Truncate transaction logs:

The code is as follows:	Copy code
Backup log Library name WITH NO_LOG

SQL 2008:

1. log cleanup in SQL2008 must be performed in simple mode. After the cleanup is completed, the log is called back to full mode. You must switch back to full mode. Otherwise, the database does not support time point backup.
1) select database-properties-options-recovery mode-select simple.
2). After the database is shrunk, the database is completely recovered.
2. You can use commands to perform operations directly.
:
Clear logs

The code is as follows:	Copy code
-- Set the database SIMPLE model. Alter database name set recovery simple; GO -- Shrink the truncated log file to 2 M Dbcc shrinkfile (log name, 2); GO -- Reset the database recovery model. alter database database DATABASE name set recovery full; GO

In the current database:

Select fileid, groupid, name from sysfiles where groupid = 0

The following result is displayed:
Fileid groupid name
2 0 test123_log

Fileid is the log file ID, and name is the log name test123_log.

Log cleanup in SQL2008 must be performed in simple mode. After the cleanup is completed, the log is called back to full mode.

Solution 1: full command mode

The code is as follows:	Copy code
USE [master] GO ALTERDATABASE DNName set recovery simple with NO_WAIT GO ALTERDATABASE DNName set recovery simple -- SIMPLE mode GO USE DNName GO Dbcc shrinkfile (n'dnname _ log', 11, TRUNCATEONLY) GO USE [master] GO ALTERDATABASE DNName set recovery fullwith NO_WAIT GO ALTERDATABASE DNName set recovery full -- restore to FULL mode GO

Solution 2: Part of the command mode + task-contraction-file (single database)

The code is as follows:	Copy code
ALTERDATABASE DNName set recovery simple -- SIMPLE mode GO

Right-click the task and choose "contract"> "file". Then, the database logs are retained for only 1 MB.

ALTERDATABASE 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.
.