SQL Server 数据库维护脚本合集[004]-清除数据库Log

--*****************************************************************************
-- @作    者:  qwu;
-- @用    途:  清除Sql Server(2000以上版本)数据库日志;
-- @版    本:  v1.0
-- @创建日期:  2011-3-23;
-- @用    法:  设置use [数据库名]，@LogicalFileName='日志文件名',@NewSize = 日志文件大小;
-- @注    意:  有时候要多执行几次才能达到效果;
-- @修改历史:  
--   2011-3-23 qwu 创建;
--*****************************************************************************

--BEGIN--
BEGIN TRAN

SET NOCOUNT ON

DECLARE @LogicalFileName sysname,

        @MaxMinutes INT,

        @NewSize INT

USE     [数据库名]               -- 要操作的数据库名

SELECT  @LogicalFileName = '日志文件名',-- 日志文件名

@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

if object_id('[DummyTransTable1234]') is not null

    DROP TABLE DummyTransTable1234

CREATE TABLE DummyTransTable1234

  (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 DummyTransTable1234 VALUES ('Fill Log')

        DELETE DummyTransTable1234

        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 DummyTransTable1234

SET NOCOUNT OFF

ROLLBACK
--END--