教你如何缩小SQL Server数据库日志文件综合教程 -电脑资料

    SET NOCOUNT ON

    DECLARE @LogicalFileName sysname,

    @MaxMinutes INT,

    @NewSize INT

    USE　　 Marias

    -- 要操作的数据库名

    SELECT　@LogicalFileName = 'Marias_log'

    -- 日志文件名

    @MaxMinutes = 10,

    -- Limit on time allowed to wrap log.

    @NewSize = 100

    -- 你想设定的日志文件的大小(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 @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 DummyTrans VALUES ('Fill Log')

    DELETE DummyTrans

    SELECT @Counter = @Counter + 1

    END

    EXEC (@TruncLog)

    关 键 字：SQLServer

    相关文章：

    SQL Server事务日志的几个常用操作

    SQL Server：存储图像和BLOB文件(一)

    SQL Server：存储图像和BLOB文件(二)

    SQL Server：存储图像和BLOB文件(三)

    SQL Server：存储图片和BLOB文件(四)

    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