缩小数据库日志另类方法_sqlserver

–功能说明：缩小数据库日志文件
–作者：不详
–e.g:数据库名为abc
————————————————————–
SET NOCOUNT ON

http://www.gaodaima.com/35271.html缩小数据库日志另类方法_sqlserver

DECLARE @LOGICALFILENAME SYSNAME,
        @MAXMINUTES INT,
        @NEWSIZE INT
USE abc—要操作的数据库
SELECT @LOGICALFILENAME = ‘abc_LOG’,—日志文件名
       @MAXMINUTES = 10,—LIMIT ON TIME ALLOWED TO WRAP LOG.
       @NEWSIZE = 1—你想设定的日志文件大小(M)
–SETUP /INITIALIZE
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 @COUNT 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 NOE EXPIRED
  AND @ORIGINALSIZE =(SELECT SIZE FROM SYSFILES WHERE NAME = @LOGICALFILENAME)
  AND (@ORIGINALSIZE*8/1024)>@NEWSIZE
BEGIN –OUTER LOOP.
    SELECT @COUNT = 0
        WHILE((@COUNT<@ORIGINALSIZE/16) AND (@COUNT<50000))
        BEGIN — UPDATE
            INSERT DUMMYTRANS VALUES (‘FILL LOG’)
            DELETE DUMMYTRANS
            SELECT @COUNT = @COUNT + 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

欢迎大家阅读《缩小数据库日志另类方法_sqlserver》,跪求各位点评,若觉得好的话请收藏本文，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)