返回列表 发帖

[技术文章] MSSQL清除或收缩数据库日志

第一种:

        用 bcp命令把数据库中的记录都导出来保存到另一台机器,然后用truncate table tablename的方式把所有记录都清空,然后执行dump transaction dbname with no_log,发现log文件已显著减少,再用bcp命令导入,导入后log文件又增大,但再用 dump transaction dbname with no_log,效果不仅是使日志占的空间减少,日志文件的size也显著减少。
        前几天也碰到日志文件过大的问题,数据库实际大小为600M, 日志文件实际大小为33M, 但日志文件占用空间为2.8G!试了多种方式,SHIRNK DATABASE, TRUNCATE LOG FILE, 都没办法将文件缩小。无论如何,这应该算SQL SERVER的一个BUG吧。
        后来找到下面的代码,就可以将日志文件缩小到自己想要的大小了。把代码COPY到查询分析器里,,然后修改其中的3个参数(数据库名,日志文件名,和目标日志文件的大小),运行即可(我已经用过多次了)

[sql] view plaincopyprint?


  • 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)   
  •   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


1.理解日志截断选项
        不同的备份日志截断选项经常被DBA新手所忽视。DBA新手通常并不知道选项存在或了解它们的作用。怎样使用或什么时候时候它们,以下的几小节详细解释每个选项的作用以及在什么时候使用它。


2.TRUNCATE_ONLY
        TRUNCATE_ONLY选项截掉事务日志的非活动部分,而不备份(拷贝)日志到备份设备上。因为日志没有拷贝,所以在使用TRUNCATE_ONLY时,不必指明备份设备。例如,用TRUNCATE_ONLY选项备份主数据库事务日志的语法如下:

[sql] view plaincopyprint?


  • Backup Log master  
  • WITH TRUNCATE_ONLY  


在以下情况下使用TRUNCATE_ONLY:
         如果你不是为了恢复目的使用事务日志,并且依赖于完整数据库备份(完整或差异)。如果没有执行数据库备份就使用TRUNCATE_ONLY选项,你将不能在带有TRUNCATE_ONLY的BACKUP LOG命令执行时,恢复事务日志非活动部分的已完成事务。

3.NO_LOG
         当有NO_LOG选项的BACKUP LOG命令执行时,SQL SERVER不记录BACKUP LOG命令,就截断事务日志的非活动部分。仅当事务日志完全填满时才使用NO_LOG选项,当日志完全填满时,你不能通过执行一条普通的BACKUP LOG命令来截断事务日志。这样是因为SQL SERVER试图记录BACKUP命令,而在事务日志中却没有剩余空间。和TRUNCATE_ONLY选项一样,NO_LOG选项不需要备份设备,因为日志并不拷贝到设备上去。 当你试图进入的数据库被破坏并打算恢复数据库时,使用NO_TRUNCATE选项。要使用NO_TRUNCATE,必须满足以下条件:
        1).事务日志必须和数据库在不同的设备上。
        2).MASTER数据库必须没有被破坏
NO_TRUNCATE记录从最近一次事务日志备份,到数据库破坏点的所有事务日志项。然后恢复事务日志备份作为最近一次备份,它在恢复过程中,可精确到毫秒级。

        很多时候,我们被数据库日志文件大小不断在增加而困挠,虽然可以用截短事务日志的命令dump transaction database_name with no_log来使日志占用实际物理log文件的空间的百分比减小,但数据库log文件的把磁盘的空间霸占着不用,使其他的程序所需的空间受到影响。为此,我做了很多次试验,以探讨能够直接减小log文件大小的方法,请方家指教!
        根据db_option中的有关选项,在不同设置时,做dump transaction database_name with no_log操作后,发现log文件的total space都不变化,只是used space变小,而free space相应变大。这样的变化意味着,你以后的日志还有可写入的空间,因为空间被预留了。但当这个log文件已经太大,而影响了其他程序的使用空间时,这样的结果并不是我需要的。网站建设
        后来我做了一个这样的操作,用bcp命令把数据库中的记录都导出来保存到另一台机器的硬盘上。然后用truncate table table_name的方式把所有的记录都清空,然后执行dump transaction database_name with no_log,发现log文件已经显著地减小,再用bcp命令将之前导出的数据导入到数据库中,导入完成后,log文件又增大了,但再用dump transaction database_name with no_log命令操作时,效果不仅是使日志占用空间减少,日志文件的size也显著地减小。网站建设
试了多种方式,SHIRNK DATABASE, TRUNCATE LOG FILE, 都没办法将文件缩小。无论如何,这应该算SQL SERVER的一个BUG吧。


第二钟:

/*
标题:清除日志和收缩日志
作者:爱新觉罗·毓华
时间:2008-07-14
地点:新疆乌鲁木齐
资料来源:深圳外经贸局年检数据库和CSDN会员adis789咨询sql server日志为啥会增长很快,
有一个程序每5秒钟写数据一次,然后数据库日志经常增长很快,怎么避免这样的事情发生呢?
怎么能让sql server日志增长不要这么快,数据文件才100M日志文件居然1G多。
*/

[sql] view plaincopyprint?


  • --清除日志:
  • DECLARE @LogicalFileName sysname,  
  •         @MaxMinutes INT,  
  •         @NewSize INT
  • USE     szwzcheck             -- 要操作的数据库名
  • SELECT  @LogicalFileName = 'szwzcheck_Log',  -- 日志文件名
  • @MaxMinutes = 10,               -- Limit on time allowed to wrap log.
  •         @NewSize = 20                  -- 你想设定的日志文件的大小(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
  •       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)   
  •   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

  • --把szwzcheck换成你数据库的名字即可,在查询分析器里面运行。

  • --收缩日志:企业管理器--所有任务--收缩数据库--文件--选日志文件收缩


第三种:

[delphi] view plaincopyprint?


  • SQL2008 的收缩日志   
  • 由于SQL2008对文件和日志管理进行了优化,所以以下语句在SQL2005中可以运行但在SQL2008中已经被取消:  
  • (SQL2005)  
  • Backup Log DNName with no_log  
  • go  
  • dump transaction DNName with no_log  
  • go  
  • USE DNName   
  • DBCC SHRINKFILE (2)  
  • Go  
  • --------------------------------------------------------------  
  • (SQL2008):  
  • 在SQL2008中清除日志就必须在简单模式下进行,等清除动作完毕再调回到完全模式。  
  • USE [master]  
  •     GO  
  •     ALTER DATABASE DNName SET RECOVERY SIMPLE WITH NO_WAIT  
  •     GO  
  •     ALTER DATABASE DNName SET RECOVERY SIMPLE   --简单模式  
  •     GO  
  •     USE DNName   
  •     GO  
  •     DBCC SHRINKFILE (N'DNName_Log' , 11, TRUNCATEONLY)  
  •     GO  
  •     USE [master]  
  •     GO  

  •     ALTER DATABASE DNName SET RECOVERY FULL WITH NO_WAIT  

  •     GO  

  •     ALTER DATABASE DNName SET RECOVERY FULL  --还原为完全模式  

  •     GO  

  • 优点:此清除日志所运行消耗的时间短,90GB的日志在分钟左右即可清除完毕,做完之后做个完全备份在分钟内  
  • 即可完成。  
  • 缺点: 不过此动作最好不要经常使用,因为它的运行会带来系统碎片。普通状态下LOG和DIFF的备份即可截断日志。  
  • 此语句使用的恰当环境:当系统的日志文件异常增大或者备份LOG时间太长可能影响生产的情况下使用。
欢迎光临:逐梦论坛

返回列表

Powered by Discuz! 7.2   论坛QQ群:逐梦论坛群

© 2001-2021 Comsenz Inc. 鲁公网安备 37120302000001号