MS SQL 清除 Transaction Log Script

SQL 7.0/2000:

BACKUP TRANSACTION DBName WITH TRUNCATE_ONLY DBCC SHRINKFILE(2,200)

SQL 2005:

BACKUP LOG DBName WITH NO_LOG

DBCC SHRINKFILE(2,200)

SQL 2008:

USE DBName

Alter Database DBName Set Recovery Simple DBCC SHRINKFILE(2,100) Alter Database DBName Set Recovery Full

SQL 2008 R2:

USE DBName;

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE DBName

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 10 MB.

DBCC SHRINKFILE (2, 10);

GO

-- Reset the database recovery model.

ALTER DATABASE DBName

SET RECOVERY FULL;

GO