DECLARE @LOGICALFILENAME SYSNAME,
@MAXMINUTES INT,
@NEWSIZE INT
USE abc---要操作的数据库
SELECT @LOGICALFILENAME = abc_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 @COUNT 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 NOE EXPIRED
AND @ORIGINALSIZE =(SELECT SIZE FROM SYSFILES WHERE NAME = @LOGICALFILENAME)
AND (@ORIGINALSIZE*8/1024)>@NEWSIZE
BEGIN --OUTER LOOP.
SELECT @COUNT = 0
WHILE((@COUNT<@ORIGINALSIZE/16) AND (@COUNT<50000))
BEGIN -- UPDATE
INSERT DUMMYTRANS VALUES (FILL LOG)
DELETE DUMMYTRANS
SELECT @COUNT = @COUNT + 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
