缩小日志另类方法

来源：互联网发布：pbx指标软件编辑：程序博客网时间：2024/05/01 18:18

--功能说明：缩小数据库日志文件
--作者：不详
--e.g:数据库名为abc
--------------------------------------------------------------
SETNOCOUNTON
DECLARE@LOGICALFILENAMESYSNAME,
       @MAXMINUTESINT,
       @NEWSIZEINT
USEabc---要操作的数据库
SELECT@LOGICALFILENAME='abc_LOG',---日志文件名
      @MAXMINUTES=10,---LIMITONTIMEALLOWEDTOWRAPLOG.
      @NEWSIZE=1---你想设定的日志文件大小(M)
--SETUP/INITIALIZE
DECLARE@ORIGINALSIZEINT
SELECT@ORIGINALSIZE=SIZE
  FROMSYSFILES
WHERENAME=@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@COUNTINT,
       @STARTTIMEDATETIME,
       @TRUNCLOGVARCHAR(255)
SELECT@STARTTIME=GETDATE(),
       @TRUNCLOG='BACKUPLOG'+DB_NAME()+'WITHTRUNCATE_ONLY'
  DBCCSHRINKFILE(@LOGICALFILENAME,@NEWSIZE)
EXEC(@TRUNCLOG)
--WRAPTHELOGIFNECESSARY
WHILE@MAXMINUTES>DATEDIFF(MI,@STARTTIME,GETDATE())--TIMEHASNOEEXPIRED
AND@ORIGINALSIZE=(SELECTSIZEFROMSYSFILESWHERENAME=@LOGICALFILENAME)
AND(@ORIGINALSIZE*8/1024)>@NEWSIZE
BEGIN--OUTERLOOP.
   SELECT@COUNT=0
       WHILE((@COUNT<@ORIGINALSIZE/16)AND(@COUNT<50000))
       BEGIN--UPDATE
           INSERTDUMMYTRANSVALUES('FILLLOG')
           DELETEDUMMYTRANS
           SELECT@COUNT=@COUNT+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

<script type="text/javascript"></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>