日志文件过大 处理方法 矫情吗;* 2022-09-26 13:59 158阅读 0赞 /\* 本文由微软新闻组摘录下来的。一段非常有用的脚本。 如果碰到日志文件过大的问题,用SHIRNK DATABASE, TRUNCATE LOG FILE不是很有效时,可以考虑试下下面的脚本。把代码COPY到查询分析器里,然后修改其中的3个参数(数据库名,日志文件名,和目标日志文件的大小),运行即可 \*/ \---------------------------------------------------------------------------------- SET NOCOUNT ON DECLARE @LogicalFileName sysname, --日志文件名 @MaxMinutes INT, --允许此脚本执行的最长时间 @NewSize INT --目标日志文件的大小 USE CRM -- 要操作的数据库名 SELECT @LogicalFileName = 'CRM\_LOG', -- 日志文件名 @MaxMinutes = 10, -- Limit on time allowed to wrap log. @NewSize = 1 -- 想要收缩到的目标大小(单位M),此处标记收缩到1M 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 \----------------------------------------------------------------------------
还没有评论,来说两句吧...