收缩数据库事务日志的过程

Md. Marufuzzaman

4.95/5 (25投票s)

2009年7月25日

CPOL

2分钟阅读

61486

326

本文将演示缩小数据库事务日志的步骤。

下载脚本 - 2.54 KB

引言

什么是缩小数据库？实际上，为了增加 SQL Server 磁盘空间，使用缩小数据库任务。本文将演示缩小数据库事务日志的步骤。

背景

偶尔，您可能需要进行数据库维护，无论您使用的是预打包的 MSDE 还是完整版本的 SQL Server。应该在您从数据库中删除大量数据后进行此维护（Example.mdf 和 Example_log.ldf）。通常，如果从事件日志中删除大量事件，则会发生这种情况。即使删除了数据，也需要压缩.mdf 和 .ldf 文件（类似于删除电子邮件后的 Outlook *.pst 文件），以便恢复磁盘空间并提高效率，并减少寻道时间。

更多详细信息请参阅此链接。

Using the Code

在我们开始使用这段代码/了解代码的工作原理之前，我想分享一些关于数据库缩小过程的基本信息。

该过程有几个控制参数，大多数时候您只需要关注前四个，因为这些是主要的控制因素，而第五个只是一个微调控制，很少需要发挥作用。

参数一览

SET @LogicalFileName =’Logical file name’; -- Your Database Name
SET @MaxMinutes = 5; --Limit on time allowed to wrap log in minutes
SET @NewSize =100; --Ideal size of logfile in MB
SET @Factor = 1.0;
/*
Factor determining maximum number of pages to pad out 
based on the original number of pages 
in use (single page = 8K). Values in the range 1.0 - 0.8 
seems to work well for many databases. 
Increasing the number will increase the maximum 
number of pages allowed to be padded, which 
should force larger amounts of data to be dropped 
before the process finishes. Often speeds up 
shrinking very large databases which are going 
through the process before the timer runs out. 
Decreasing the number will decrease the maximum number 
of pages allowed to be padded, which 
should force less work to be done. Often aids with 
forcing smaller databases to shrink to minimum 
size when larger values were actually expanding them.
*/

DBCC SHRINKDATABASE

缩小指定数据库中数据和日志文件的大小。

更多详细信息请参阅此链接。

使用这段代码非常简单。只需按照给定的步骤操作

设置您的数据库名称。
示例：这是将要缩小的数据库的名称。
USE [databasename] 将数据库的逻辑名称替换数据库名称。
设置逻辑文件名。
示例：使用 sp_helpfile 来识别您想要缩小的逻辑文件名。
```
SET @LogicalFileName = 'database_Log';
```

*在这里做同样的操作，将 database_log 替换为数据库日志名称。

*运行脚本，如果脚本无法运行，则可能是数据库的逻辑名称不正确。

注意：这已在 SQL 2000、7 和 2005 数据库上成功测试。40 GB 减少到 1 GB 以下，176GB 减少到 105MB。

代码示例如下

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
EXEC dbo.spShrinSQLServerTransactionLog 'TempE'
*/

-- =============================================
-- Author:        <Author,,Md. Marufuzzaman>
-- Create date: <Create Date,,24/07/2009>
-- Description:    <Description,, 
-- How to Shrink the SQL Server Transaction Log Using T-SQL>
-- =============================================

CREATE PROCEDURE [dbo].[spShrinSQLServerTransactionLog] 
 @DB_NAME  VARCHAR(100)
AS
BEGIN

/*
Shrink a named transaction log file belonging to a database

Originally found at;

http://support.microsoft.com/support/kb/articles/q256/6/50.asp

Changes:
28.04.2004
Modified the inner loop so it tested the dx time so long overruns did not happen
Modified the inner loop so it had a fixed minimum quantity 
			so there was no skip in skip out

25.07.2009
Reodified the inner loop so it had a dynamic minimum quantity to allow faster shrinkage

25.07.2009
Reodified the varchar type data conversion

*/

SET NOCOUNT ON

DECLARE  @LogicalFileName SYSNAME
        ,@MaxMinutes INT
        ,@NewSize INT
        ,@Factor FLOAT

/*
The process has several control parameters, 
most of the time you only need to worry about the first four
as these are the big controls whereas the fifth is 
simply a fine tuning control which rarely needs to 
come into play.
*/

--This is the name of the database for which the log will be shrunk.
--USE  @DB_NAME

--Use sp_helpfile to identify the logical file name that you want to shrink.
SET @LogicalFileName = @DB_NAME;
--Limit on time allowed to wrap log in minutes
SET @MaxMinutes = 5;
--Ideal size of logfile in MB
SET @NewSize =100;

/*
Factor determining maximum number of pages to pad out 
based on the original number of pages in use 
(single page = 8K).  Values in the range 1.0 - 0.8 seems to work well for many databases.

Increasing the number will increase the maximum number 
of pages allowed to be padded, which should
force larger amounts of data to be dropped before 
the process finishes.  Often speeds up shrinking
very large databases which are going through the process before the timer runs out.

Decreasing the number will decrease the maximum 
number of pages allowed to be padded, which should
force less work to be done.  Often aids with forcing 
smaller databases to shrink to minimum size
when larger values were actually expanding them.

*/
SET @Factor = 1.0;                        

/*
All code after this point is driven by these parameters 
and will not require editing unless you need to 
fix a bug in the padding/shrinking process itself.
*/

-- Setup / initialize
DECLARE @OriginalSize INT,
        @StringData VARCHAR(500)

SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName;

SELECT @StringData = 'Original Size of ' + db_name() + ' LOG is ' + 
    CONVERT(VARCHAR,@OriginalSize) + ' 8K pages or ' + 
    CONVERT(VARCHAR,(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName;

PRINT @StringData;
PRINT ''

--Drop the temporary table if it already exists
IF ( OBJECT_ID('[dbo].[DummyTransaction]') IS NOT NULL )
  DROP TABLE [DummyTransaction]

CREATE TABLE [DummyTransaction]( [DummyColumn] CHAR(8000) NOT NULL );

DECLARE @Counter   INT,
        @MaxCount  INT,
        @StartTime DATETIME,
        @TruncLog  VARCHAR(500)

-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)

SET @TruncLog = 'BACKUP LOG [' + db_name() + '] WITH TRUNCATE_ONLY';
EXEC (@TruncLog)

-- Configure limiter
IF @OriginalSize / @Factor > 50000
    SET @MaxCount = 50000
ELSE
    SET @MaxCount = @OriginalSize * @Factor

-- Attempt to shrink down the log file
PRINT 'Minimum Quantity : '+ CAST( @MaxCount AS VARCHAR(10) )
PRINT 'Maximum Time : '+ CAST( @MaxMinutes AS VARCHAR(10) )+' _
	minutes ('+CAST( @MaxMinutes*60 AS VARCHAR(10) )+' seconds)'
PRINT ''

SET @Counter = 0;
SET @StartTime = GETDATE();

--loop the padding code to reduce the log while
-- within time limit and 
-- log has not been shrunk enough
WHILE (
    (@MaxMinutes*60 > DATEDIFF(ss, @StartTime, GETDATE())) AND
    (@OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)) AND
    ((@OriginalSize * 8 / 1024) > @NewSize)
)
BEGIN --Outer loop.

    --pad out the logfile a page at a time while
    -- number of pages padded does not exceed our maximum page padding limit
    -- within time limit and 
    -- log has not been shrunk enough
    WHILE (
        (@Counter < @MaxCount) AND 
        (@MaxMinutes*60 > DATEDIFF(ss, @StartTime, GETDATE())) AND
        (@OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)) AND
        ((@OriginalSize * 8 / 1024) > @NewSize)
    )
    BEGIN --Inner loop
        
        INSERT INTO [DummyTransaction] VALUES ('Fill Log')  -- Because it is 
					-- a char field it inserts 8000 bytes.
        DELETE FROM [DummyTransaction]
        SELECT @Counter = @Counter + 1

        --Every 1,000 cycles tell the user what is going on
        IF ROUND( @Counter , -3 ) = @Counter
        BEGIN
            PRINT 'Padded '+LTRIM( CAST( @Counter*8 AS VARCHAR(10) ) )+_
		'K @ '+LTRIM( CAST( DATEDIFF( ss, @StartTime, GETDATE() ) _
		AS VARCHAR(10) ) )+' seconds';
        END
    END

    --See if a trunc of the log shrinks it.
    EXEC( @TruncLog )

END
PRINT ''

SELECT @StringData = '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;

PRINT @StringData
PRINT ''

DROP TABLE [DummyTransaction];
PRINT '*** Perform a full database backup ***'

SET NOCOUNT OFF

END

GO

结论

我希望本文对您有所帮助。祝您愉快！

历史

2009 年 7 月 25 日：初始发布