SQL Server 数据库清除日志的方法

SET NOCOUNT ON 
DECLARE @LogicalFileName sysname, 
@MaxMinutes INT, 
@NewSize INT 

USE tablename -- 要操作的数据库名 
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 
@MaxMinutes = 10, -- Limit on time allowed to wrap log. 
@NewSize = 1 -- 你想设定的日志文件的大小(M) 

-- Setup / initialize 
DECLARE @OriginalSize int 
SELECT @OriginalSize = size 
FROM sysfiles 
WHERE name = @LogicalFileName 
SELECT 'Original Size of ' + db_name() + ' LOG is ' + 
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' 
FROM sysfiles 
WHERE name = @LogicalFileName 
CREATE TABLE DummyTrans 
(DummyColumn char (8000) not null) 

DECLARE @Counter INT, 
@StartTime DATETIME, 
@TruncLog VARCHAR(255) 
SELECT @StartTime = GETDATE(), 
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' 

DBCC SHRINKFILE (@LogicalFileName, @NewSize) 
EXEC (@TruncLog) 
-- Wrap the log if necessary. 
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired 
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) 
AND (@OriginalSize * 8 /1024) > @NewSize 
BEGIN -- Outer loop. 
SELECT @Counter = 0 
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) 
BEGIN -- update 
INSERT DummyTrans VALUES ('Fill Log') 
DELETE DummyTrans 
SELECT @Counter = @Counter + 1 
END 
EXEC (@TruncLog) 
END 
SELECT 'Final Size of ' + db_name() + ' LOG is ' + 
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' 
FROM sysfiles 
WHERE name = @LogicalFileName 
DROP TABLE DummyTrans 
SET NOCOUNT OFF 

dump transaction 数据库名称 with no_log 
backup log 数据库名称 with no_log 
dbcc shrinkdatabase(数据库名称) 

sp_dboption 数据库名称, "trunc. log on chkpt.", true 
checkpoint 
sp_dboption 数据库名称, "autoshrink", true 

EXEC sp_attach_db @dbname = N'database_name', 
@filename1 = N'e:\mssql7\data\database_name_data.mdf' 

*******下面是转发的邮件***** 

The shrinking of log files is not immediate in SQL Server 7.0. The 
shrinking of log files does not occur until the active portion of the 
log moves. As updates are performed on the database, the shrink 
operation occurs at checkpoints or transaction log backups. Each log 
file is marked with the target_percent for the shrink operation. Each 
subsequent log backup or log truncation attempts to shrink the file to 
bring its size as close to the target_percent as possible. Because a log 
file can be shrunk only to a virtual log file boundary, it may not be 
possible to shrink a log file to a size smaller than the size of a 
virtual log file even if it is not being used. Please refer to SQL Book 
Online for the details. 

RESOLUTION 

Below script will help to shrink the log file immediately, pls keep it 
running for 3~4 minutes and then stop it manually. 

\* Run "select fileid, name,filename from ..sysfiles" to get 
the fileid which you want to shrink *\ 

use 
go 
dbcc shrinkfile(fileid,notruncate) 
dbcc shrinkfile(fileid,truncateonly) 
create table t1 (char1 char(4000)) 
go 
declare @i int 
select @i = 0 
while (1 = 1) 
begin 
while (@i < 100) 
begin 
insert into t1 values ('a') select @i = @i +1 
end 
truncate table t1 
backup log with truncate_only 
end 
go 

*****转发内容结束*****