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