如何缩小SQL数据库日记的大小.doc

如何缩小SQL数据库日记的大小以下是引用片段：缩小SQL数据库日记的大小一般情况下，当SQL数据库日记文件太大时，很难将其压缩到更小。通过下面的这段批处理SQL程序序就可以实现了。(试用通过!)注：修改下面的三个备注处的参数即可COPY到查询分析器里执行了。SET NOCOUNT ONDECLARE LogicalFileName sysname,MaxMinutes INT,NewSize INTUSE dvbbs- 要操作的数据库名SELECT LogicalFileName = dvbbs_log, - 日志文件名MaxMinutes = 10, - Limit on time allowed to wrap log.NewSize = 20 - 你想设定的日志文件的大小(M)- Setup / initializeDECLARE OriginalSize intSELECT OriginalSize = sizeFROM sysfilesWHERE name = LogicalFileNameSELECT Original Size of + db_name() + LOG is +CONVERT(VARCHAR(30),OriginalSize) + 8K pages or +CONVERT(VARCHAR(30),(OriginalSize*8/1024) + MBFROM sysfilesWHERE name = LogicalFileNameCREATE 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_ONLYDBCC SHRINKFILE (LogicalFileName, NewSize)EXEC (TruncLog)- Wrap the log if necessary.WHILE MaxMinutes DATEDIFF (mi, StartTime, GETDATE() - time has not expiredAND OriginalSize = (SELECT size FROM sysfiles WHERE name = LogicalFileName)AND (OriginalSize * 8 /1024) NewSizeBEGIN - Outer loop.SELECT Counter = 0WHILE (Counter OriginalSize / 16) AND (Counter 50000)BEGIN - updateINSERT DummyTrans valueS (Fill Log)DELETE DummyTransSELECT Counter = Counter + 1ENDEXEC (TruncLog)ENDSELECT Final Size of + db_name() + LOG is +CONVERT(VARCHAR(30),size) + 8K pages or +CONVERT(VARCHAR(30),(size*8/1024) + MBFR