MSSQL清除或收缩数据库日志
[p=26, 2, left]第一种：[/p][p=26, 2, left] 用 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个参数(数据库名，日志文件名，和目标日志文件的大小)，运行即可(我已经用过多次了)
[/p][font=Consolas,][font=Verdana, Geneva, Arial, Helvetica, sans-serif][size=9px][b][sql][/b] [url=http://blog.csdn.net/mh942408056/article/details/10170051#]view plain[/url][url=http://blog.csdn.net/mh942408056/article/details/10170051#]copy[/url][url=http://blog.csdn.net/mh942408056/article/details/10170051#]print[/url][url=http://blog.csdn.net/mh942408056/article/details/10170051#]?[/url]
[/size][/font]
[list=1][*][b]SET[/b] NOCOUNT [b]ON[/b]
[*][b]DECLARE[/b] @LogicalFileName sysname, [*] @MaxMinutes [b]INT[/b], [*] @NewSize [b]INT[/b]
[*]
[*]
[*]USE Marias -- 要操作的数据库名
[*][b]SELECT[/b] @LogicalFileName = 'Marias_log', -- 日志文件名
[*]@MaxMinutes = 10, -- Limit on time allowed to wrap log.
[*] @NewSize = 100 -- 你想设定的日志文件的大小(M)
[*]
[*]-- Setup / initialize
[*][b]DECLARE[/b] @OriginalSize [b]int[/b]
[*][b]SELECT[/b] @OriginalSize = [b]size[/b]
[*] [b]FROM[/b] sysfiles [*] [b]WHERE[/b]
[b]name[/b] = @LogicalFileName [*][b]SELECT[/b]
'Original Size of ' + db_name() + ' LOG is ' + [*] CONVERT([b]VARCHAR[/b](30),@OriginalSize) + ' 8K pages or ' + [*] CONVERT([b]VARCHAR[/b](30),(@OriginalSize*8/1024)) + 'MB'
[*] [b]FROM[/b] sysfiles [*] [b]WHERE[/b]
[b]name[/b] = @LogicalFileName [*][b]CREATE[/b]
[b]TABLE[/b] DummyTrans [*] (DummyColumn [b]char[/b] (8000) not
null) [*]
[*]
[*][b]DECLARE[/b] @Counter [b]INT[/b], [*] @StartTime DATETIME, [*] @TruncLog [b]VARCHAR[/b](255) [*][b]SELECT[/b] @StartTime = GETDATE(), [*] @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
[*]
[*]DBCC SHRINKFILE (@LogicalFileName, @NewSize) [*][b]EXEC[/b] (@TruncLog) [*]-- Wrap the log if necessary.
[*]WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
[*] AND @OriginalSize = ([b]SELECT[/b]
[b]size[/b]
[b]FROM[/b] sysfiles [b]WHERE[/b]
[b]name[/b] = @LogicalFileName) [*] AND (@OriginalSize * 8 /1024) > @NewSize [*] [b]BEGIN[/b]
-- Outer loop.
[*] [b]SELECT[/b] @Counter = 0 [*] WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) [*] [b]BEGIN[/b]
-- update
[*] [b]INSERT[/b] DummyTrans [b]valueS[/b] ('Fill Log') [*] [b]DELETE[/b] DummyTrans [*] [b]SELECT[/b] @Counter = @Counter + 1 [*] [b]END[/b]
[*] [b]EXEC[/b] (@TruncLog) [*] [b]END[/b]
[*][b]SELECT[/b]
'Final Size of ' + db_name() + ' LOG is ' + [*] CONVERT([b]VARCHAR[/b](30),[b]size[/b]) + ' 8K pages or ' + [*] CONVERT([b]VARCHAR[/b](30),([b]size[/b]*8/1024)) + 'MB'
[*] [b]FROM[/b] sysfiles [*] [b]WHERE[/b]
[b]name[/b] = @LogicalFileName [*][b]DROP[/b]
[b]TABLE[/b] DummyTrans [*][b]SET[/b] NOCOUNT [b]OFF[/b]
[/list][/font]
[p=26, 2, left]1.理解日志截断选项
不同的备份日志截断选项经常被DBA新手所忽视。DBA新手通常并不知道选项存在或了解它们的作用。怎样使用或什么时候时候它们，以下的几小节详细解释每个选项的作用以及在什么时候使用它。[/p][p=26, 2, left]
2.TRUNCATE_ONLY
TRUNCATE_ONLY选项截掉事务日志的非活动部分，而不备份（拷贝）日志到备份设备上。因为日志没有拷贝，所以在使用TRUNCATE_ONLY时，不必指明备份设备。例如，用TRUNCATE_ONLY选项备份主数据库事务日志的语法如下：[/p][font=Consolas,][font=Verdana, Geneva, Arial, Helvetica, sans-serif][size=9px][b][sql][/b] [url=http://blog.csdn.net/mh942408056/article/details/10170051#]view plain[/url][url=http://blog.csdn.net/mh942408056/article/details/10170051#]copy[/url][url=http://blog.csdn.net/mh942408056/article/details/10170051#]print[/url][url=http://blog.csdn.net/mh942408056/article/details/10170051#]?[/url]
[/size][/font]
[list=1][*]Backup Log master [*][b]WITH[/b] TRUNCATE_ONLY [/list][/font]
[p=26, 2, left]在以下情况下使用TRUNCATE_ONLY：
如果你不是为了恢复目的使用事务日志，并且依赖于完整数据库备份（完整或差异）。如果没有执行数据库备份就使用TRUNCATE_ONLY选项，你将不能在带有TRUNCATE_ONLY的BACKUP LOG命令执行时，恢复事务日志非活动部分的已完成事务。
[/p][p=26, 2, left]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吧。[/p]
[p=26, 2, left]第二钟：[/p][p=26, 2, left]/*
标题：清除日志和收缩日志
作者：爱新觉罗·毓华
时间：2008-07-14
地点：新疆乌鲁木齐
资料来源：深圳外经贸局年检数据库和CSDN会员adis789咨询sql server日志为啥会增长很快，
有一个程序每5秒钟写数据一次，然后数据库日志经常增长很快，怎么避免这样的事情发生呢？
怎么能让sql server日志增长不要这么快，数据文件才100M日志文件居然1G多。
*/[/p][font=Consolas,][font=Verdana, Geneva, Arial, Helvetica, sans-serif][size=9px][b][sql][/b] [url=http://blog.csdn.net/mh942408056/article/details/10170051#]view plain[/url][url=http://blog.csdn.net/mh942408056/article/details/10170051#]copy[/url][url=http://blog.csdn.net/mh942408056/article/details/10170051#]print[/url][url=http://blog.csdn.net/mh942408056/article/details/10170051#]?[/url]
[/size][/font]
[list=1][*]--清除日志：
[*][b]DECLARE[/b] @LogicalFileName sysname, [*] @MaxMinutes [b]INT[/b], [*] @NewSize [b]INT[/b]
[*]USE szwzcheck -- 要操作的数据库名
[*][b]SELECT[/b] @LogicalFileName = 'szwzcheck_Log', -- 日志文件名
[*]@MaxMinutes = 10, -- Limit on time allowed to wrap log.
[*] @NewSize = 20 -- 你想设定的日志文件的大小(M)
[*]-- Setup / initialize
[*][b]DECLARE[/b] @OriginalSize [b]int[/b]
[*][b]SELECT[/b] @OriginalSize = [b]size[/b]
[*][b]FROM[/b] sysfiles [*][b]WHERE[/b]
[b]name[/b] = @LogicalFileName [*][b]SELECT[/b]
'Original Size of ' + db_name() + ' LOG is ' + [*] CONVERT([b]VARCHAR[/b](30),@OriginalSize) + ' 8K pages or ' + [*] CONVERT([b]VARCHAR[/b](30),(@OriginalSize*8/1024)) + 'MB'
[*][b]FROM[/b] sysfiles [*][b]WHERE[/b]
[b]name[/b] = @LogicalFileName [*][b]CREATE[/b]
[b]TABLE[/b] DummyTrans(DummyColumn [b]char[/b] (8000) not
null) [*][b]DECLARE[/b] @Counter [b]INT[/b], [*] @StartTime DATETIME, [*] @TruncLog [b]VARCHAR[/b](255) [*][b]SELECT[/b] @StartTime = GETDATE(), [*] @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
[*]DBCC SHRINKFILE (@LogicalFileName, @NewSize) [*][b]EXEC[/b] (@TruncLog) [*]-- Wrap the log if necessary.
[*]WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time
[*] AND @OriginalSize = ([b]SELECT[/b]
[b]size[/b]
[b]FROM[/b] sysfiles [b]WHERE[/b]
[b]name[/b] = [*]@LogicalFileName) AND (@OriginalSize * 8 /1024) > @NewSize [*] [b]BEGIN[/b]
-- Outer loop.
[*] [b]SELECT[/b] @Counter = 0 [*] WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) [*] [b]BEGIN[/b]
-- update
[*] [b]INSERT[/b] DummyTrans [b]VALUES[/b] ('Fill Log') [*] [b]DELETE[/b] DummyTrans [*] [b]SELECT[/b] @Counter = @Counter + 1 [*] [b]END[/b]
[*] [b]EXEC[/b] (@TruncLog) [*] [b]END[/b]
[*][b]SELECT[/b]
'Final Size of ' + db_name() + ' LOG is ' + [*] CONVERT([b]VARCHAR[/b](30),[b]size[/b]) + ' 8K pages or ' + [*] CONVERT([b]VARCHAR[/b](30),([b]size[/b]*8/1024)) + 'MB'
[*][b]FROM[/b] sysfiles [*][b]WHERE[/b]
[b]name[/b] = @LogicalFileName [*][b]DROP[/b]
[b]TABLE[/b] DummyTrans [*][b]SET[/b] NOCOUNT [b]OFF[/b]
[*]
[*]--把szwzcheck换成你数据库的名字即可，在查询分析器里面运行。
[*]
[*]--收缩日志：企业管理器--所有任务--收缩数据库--文件--选日志文件收缩
[/list][/font]
[p=26, 2, left]
第三种：[/p][font=Consolas,][font=Verdana, Geneva, Arial, Helvetica, sans-serif][size=9px][b][delphi][/b] [url=http://blog.csdn.net/mh942408056/article/details/10170051#]view plain[/url][url=http://blog.csdn.net/mh942408056/article/details/10170051#]copy[/url][url=http://blog.csdn.net/mh942408056/article/details/10170051#]print[/url][url=http://blog.csdn.net/mh942408056/article/details/10170051#]?[/url]
[/size][/font]
[list=1][*]SQL2008 的收缩日志 [*]由于SQL2008对文件和日志管理进行了优化，所以以下语句在SQL2005中可以运行但在SQL2008中已经被取消： [*](SQL2005) [*]Backup Log DNName [b]with[/b] no_log [*]go [*]dump transaction DNName [b]with[/b] 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时间太长可能影响生产的情况下使用。[/list][/font]
页:
[1]