MS Sql Server 日志清理-优快云博客

本文介绍了一种在SQL Server中手动调整日志文件大小的方法。通过创建临时表并进行更新删除操作来填充日志文件，之后使用TRUNCATE_ONLY选项备份日志以达到缩小日志文件的目的。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SETNOCOUNTON
DECLARE@LogicalFileNamesysname,@MaxMinutesINT,@NewSizeINT
USEgrades--要操作的数据库名
SELECT@LogicalFileName='grades_log',--日志文件名
@MaxMinutes=10,--Limitontimeallowedtowraplog.
@NewSize=1--你想设定的日志文件的大小(M)

--Setup/initialize
DECLARE@OriginalSizeint
SELECT@OriginalSize=sizeFROMsysfilesWHEREname=@LogicalFileName

SELECT'OriginalSizeof'+db_name()+'LOGis'+
CONVERT(VARCHAR(30),@OriginalSize)+'8Kpagesor'+
CONVERT(VARCHAR(30),(@OriginalSize*8/1024))+'MB'
FROMsysfiles
WHEREname=@LogicalFileName

CREATETABLEDummyTrans(DummyColumnchar(8000)notnull)
DECLARE@CounterINT,@StartTimeDATETIME,@TruncLogVARCHAR(255)

SELECT@StartTime=GETDATE(),@TruncLog='BACKUPLOG'+db_name()+'WITHTRUNCATE_ONLY'
DBCCSHRINKFILE(@LogicalFileName,@NewSize)
EXEC(@TruncLog)
--Wrapthelogifnecessary.
WHILE@MaxMinutes>DATEDIFF(mi,@StartTime,GETDATE())--timehasnotexpired
AND@OriginalSize=(SELECTsizeFROMsysfilesWHEREname=@LogicalFileName)
AND(@OriginalSize*8/1024)>@NewSize

BEGIN--Outerloop.
SELECT@Counter=0
WHILE((@Counter<@OriginalSize/16)AND(@Counter<50000))
BEGIN--update
INSERTDummyTransVALUES('FillLog')
DELETEDummyTrans
SELECT@Counter=@Counter+1
END
EXEC(@TruncLog)
END

SELECT'FinalSizeof'+db_name()+'LOGis'+
CONVERT(VARCHAR(30),size)+'8Kpagesor'+
CONVERT(VARCHAR(30),(size*8/1024))+'MB'
FROMsysfiles
WHEREname=@LogicalFileName

DROPTABLEDummyTrans
SETNOCOUNTOFF