清理清除收缩SQL日志的方法

清理清除收缩SQL日志的方法2010-07-0311:52sql2000事务日志的介绍及收缩1、事务日志简介作为大型的关系型数据库，最重要的事情之一就是数据库完整性，也就是说数据必须是正确的，不能因为一些不可知的突发事件（如突然掉电）损坏数据。为了避免这种情况，SQL数据库采用了两步写入磁盘的办法，第一步先写我做了什么，比如开了一个进货单，这一步完成后，再执行第二步，把这个进货单的内容写入数据库，这两步都成功了，才算完成，有一步有问题，这个单据都不会记到数据库中。这样，您的事务操作（对数据库的修改操作，查询不记事务）的每一步都要记录进日志文件，如果您不注意经常截断事务日志的话，随着时间的增长，事务日志会很快的增长，最后要比您的数据量大的多。可以经常看到，数据库文件只有几十兆，而日志文件却几千兆的情况。2、截断事务日志事务日志分成两部分，一部分是不活动的，也就是说这部分的操作都已经成功的写入数据库了，而另一部分是活动的，也就是说，这部分日志，只是确认执行了上述第一步，还没有确信已经写入数据库的部分。我们需要截断的就是不活动的部分。这部分可以如下方法截断：BackupLog‘数据库名’withtruncate_only警告：在运行上述截断日志之后，强烈建议进行数据库完全备份。3、缩小文件大小使用上述第2步就把不活动的日志删除了，但是文件并没有变小，只是把原先旧日志占用的空间释放了，又可以向里面记入新的日志了。要想把物理文件也变小（比如已经几千兆了），请使用如下命令：DBCCShrinkdatabase(数据库名)运行上述命令后就可以把日志文件缩小了（一般都缩为1M左右）4、把数据文件或日志文件的增长不受限制如果您做不到经常维护数据库，不能经常的截断日志，日志文件就会逐渐变大，一旦大到您设置的大小限制，日志就再也不能写入了，您对数据库的所有改变操作也就无法进行了，这时候您就需要用上面的方法把日志文件截断。不过，您可以把文件的大小设置大一些或不受限制，请在企业管理器中设置数据库属性即可。也可以运行下述命令：alterdatabase数据库名modifyfile(name=逻辑文件名,Maxsize=Unlimited)此处的逻辑文件名就是您在企业管理器中看到的文件名。说明：微软公司的SQL2000有个小错误，有时候会报告逻辑文件名不存在的问题，这时候您就应该看看真正的逻辑文件名是什么，如果您的数据库是从一个备份文件中还原的，那么可以使用下面的命令看看逻辑文件名到底是什么，此处假设您已经把备份文件复制到C盘的根目录，备份文件名为Database.bak：restorefilelistonlyfromdisk=‘c:\Database.bak’运行上述命令后，可以看出所有的逻辑文件名（LogicalName列的内容），取出这里的逻辑文件名，比如“真文件名”，运行如下命令alterdatabase数据库名modifyfile(name=真文件名,Maxsize=Unlimited)这个命令就会成功，再到企业管理器中去看，就发现文件大小已经无限制了。5、数据库日常维护为了让数据库更好的工作，不至于影响业务，日常需要对数据库进行维护，一般分为以下几个方面：a.日常的备份。建议每天进行备份，如果数据库太大，每天全库备份不现实，可以采用一周完全备份一次，每天差异备份一次或两次b.维护索引。索引能够大幅的提高查询性能，但是随着数据碎片的增加，性能会越来越差，所以要经常的维护索引，可以根据具体情况，每周或每月维护一次索引。c.截断日志。根据业务量的大小，可以不定期的截断日志。（方法参照下面）方法一：在SQL2000中，每个数据库事实上存在两个物理文件，分别为.mdf和.ldf的扩展名。前者是数据文件，后者就是保存事务日志的文件。事务日志是SQL2000中用于记录数据库操作记录的功能，不过如果长时间不进行维护，这个文件就会越来越庞大，必须清除一下。上次我的服务器上一个网站，因为数据库比较庞大（2G多），加上长时间没有维护，导致后来事务日志文件达到数十GB，把保存数据库文件的分区都给撑爆了。当时我没有过清除事务日志的经验，上网找了一些资料，很多都把这事说得很复杂，要在查询分析器里运行若干个命令什么的。其实后来我找到了一个最简单的方法，只要在查询分析器里运行一条命令，再在企业管理器里进行一点小操作就行了。步骤如下：一、运行查询分析器，运行下面这条命令（把命令中的“数据库名”替换成要清除日志的数据库名称）：DUMPTRANSACTION数据库名WITHNO_LOG二、打开企业管理器，找到这个数据库，右键点击，选择“所有任务”-“收缩数据库”：如图：三、在“收缩数据库”的对话框中，点击下面的“文件”按钮：三、在接下来的对话框中，在顶部的“数据库文件”后面选择以“_log”结尾的文件，这个是数据库的日志文件。然后直接点击下面的确定按钮。四、出现收缩成功的提示之后，再次在企业管理器中在这个数据库上点击右键查看属性，选择“事务日志”标签页，可以看到事务日志已经被成功清空了。方法二：1、首先备份数据库然后备份文件，备份日志文件，可改名在查询分析器里执行sp_attach_single_file_db,将生成新的日志文件具体怎么做，我也没做过，让有经验的人回答。，我去收集一下这方面的资料从大洋网摘录的方法，未试过用bcp命令把数据库中的记录都导出来保存到另一台机器，然后用truncatetabletablename的方式把所有记录都清空，然后执行dumptransactiondbnamewithno_log,发现log文件已显著减少，再用bcp命令导入，导入后log文件又增大，但再用dumptransactiondbnamewithno_log,效果不仅是使日志占的空间减少，日志文件的size也显著减少。前几天也碰到日志文件过大的问题，数据库实际大小为600M,日志文件实际大小为33M,但日志文件占用空间为2.8G!!!试了多种方式，SHIRNKDATABASE，TRUNCATELOGFILE,都没办法将文件缩小。无论如何，这应该算SQLSERVER的一个BUG吧。后来找到下面的代码，就可以将日志文件缩小到自己想要的大小了。把代码COPY到查询分析器里，，然后修改其中的3个参数(数据库名，日志文件名，和目标日志文件的大小)，运行即可(我已经用过多次了)-----SETNOCOUNTONDECLARE@LogicalFileNamesysname,@MaxMinutesINT,@NewSizeINTUSEMarias--要操作的数据库名SELECT@LogicalFileName='Marias_log',--日志文件名@MaxMinutes=10,--Limitontimeallowedtowraplog.@NewSize=100--你想设定的日志文件的大小(M)--Setup/initializeDECLARE@OriginalSizeintSELECT@OriginalSize=sizeFROMsysfilesWHEREname=@LogicalFileNameSELECT'OriginalSizeof'+db_name()+'LOGis'+CONVERT(VARCHAR(30),@OriginalSize)+'8Kpagesor'+CONVERT(VARCHAR(30),(@OriginalSize*8/1024))+'MB'FROMsysfilesWHEREname=@LogicalFileNameCREATETABLEDummyTrans(DummyColumnchar(8000)notnull)DECLARE@CounterINT,@StartTimeDATETIME,@TruncLogVARCHAR(255)SELECT@StartTime=GETDATE(),@TruncLog='BACKUPLOG'+db_name()+'WITHTRUNCATE_ONLY'DBCCSHRINKFILE(@LogicalFileName,@NewSize)EXEC(@TruncLog)--Wrapthelogifnecessary.WHILE@MaxMinutesDATEDIFF(mi,@StartTime,GETDATE())--timehasnotexpiredAND@OriginalSize=(SELECTsizeFROMsysfilesWHEREname=@LogicalFileName)AND(@OriginalSize*8/1024)@NewSizeBEGIN--Outerloop.SELECT@Counter=0WHILE((@Counter@OriginalSize/16)AND(@Counter50000))BEGIN--updateINSERTDummyTransvalueS('FillLog')DELETEDummyTransSELECT@Counter=@Counter+1ENDEXEC(@TruncLog)ENDSELECT'FinalSizeof'+db_name()+'LOGis'+CONVERT(VARCHAR(30),size)+'8Kpagesor'+CONVERT(VARCHAR(30),(size*8/1024))+'MB'FROMsysfilesWHEREname=@LogicalFileNameDROPTABLEDummyTransSETNOCOUNTOFF理解日志截断选项不同的备份日志截断选项经常被DBA新手所忽视。DBA新手通常并不知道选项存在或了解它们的作用。怎样使用或什么时候时候它们，以下的几小节详细解释每个选项的作用以及在什么时候使用它。TRUNCATE_ONLYTRUNCATE_ONLY选项截掉事务日志的非活动部分，而不备份（拷贝）日志到备份设备上。因为日志没有拷贝，所以在使用TRUNCATE_ONLY时，不必指明备份设备。例如，用TRUNCATE_ONLY选项备份主数据库事务日志的语法如下：BackupLogmasterWITHTRUNCATE_ONLY在以下情况下使用TRUNCATE_ONLY：如果你不是为了恢复目的使用事务日志，并且依赖于完整数据库备份（完整或差异）。如果没有执行数据库备份就使用TRUNCATE_ONLY选项，你将不能在带有TRUNCATE_ONLY的BACKUPLOG命令执行时，恢复事务日志非活动部分的已完成事务。NO_LOG当有NO_LOG选项的BACKUPLOG命令执行时，SQLSERVER不记录BACKUPLOG命令，就截断事务日志的非活动部分。仅当事务日志完全填满时才使用NO_LOG选项，当日志完全填满时，你不能通过执行一条普通的BACKUPLOG命令来截断事务日志。这样是因为SQLSERVER试图记录BACKUP命令，而在事务日志中却没有剩余空间。和TRUNCATE_ONLY选项一样，NO_LOG选项不需要备份设备，因为日志并不拷贝到设备上去。网站建设NO_TRUNCATE当你试图进入的数据库被破坏并打算恢复数据库时，使用NO_TRUNCATE选项。要使用NO_TRUNCATE，必须满足以下条件：事务日志必须和数据库在不同的设备上。MASTER数据库必须没有被破坏NO_TRUNCATE记录从最近一次事务日志备份，到数据库破坏点的所有事务日志项。然后恢复事务日志备份作为最近一次备份，它在恢复过程中，可精确到毫秒级。很多时候，我们被数据库日志文件大小不断在增加而困挠，虽然可以用截短事务日志的命令dumptransactiondatabase_namewithno_log来使日志占用实际物理log文件的空间的百分比减小，但数据库log文件的把磁盘的空间霸占着不用，使其他的程序所需的空间受到影响。为此，我做了很多次试验，以探讨能够直接减小log文件大小的方法，请方家指教！网站建设根据db_option中的有关选项，在不同设置时，做dumptransactionda