MS Sql Server 日志清理

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