SqlServer日志文件过大的处理方法

最近遇到某个MSSqlServer2008 数据库的日志文件过于庞大，数据文件4G，日志文件达到80G。

日志文件这么大的原因还得从数据库的恢复模式说起：

三种恢复模式：简单恢复模式、完整恢复模式和大容量日志恢复模式。通常，数据库使用完整恢复模式或简单恢复模式。而大容量模作为完整模式的补充，比如需要一次性导入大量数据，这会产生大量日志，可以暂时切换到大容量日志模式可以提高性能，减少日志大小。大容量日志模式为附加模块不常用，所以主要介绍简单和完整模式：

简单恢复模式是无日志备份的。在这种模式下会自动回收日志空间以减少空间需求，实际上不再需要管理事务日志空间。最新备份之后的更改不受保护。在发生灾难时，这些更改必须重做。只能恢复到上次的备份。 完整模式是需要日志备份。数据文件丢失或损坏不会导致丢失工作。可以恢复到任意时点（例如应用程序或用户错误之前）。但是如果日志尾部损坏，则必须重做自最新日志备份之后所做的更改。

查看现场恢复模式为完整恢复模式，所有操作都记录log，所以日志才会这么大。（BTW：现场数据文件和日志文件在同一目录下，不建议这样做，最好放到不同的分区，这样如果一个分区坏了，还可以恢复，否则....）。

查询资料网上说收缩文件，使用了效果不理想。继续查询资料得出结果:要先截断日志再收缩。例如我拿了另外相对较小的一个数据库做测试.

开始时候数据库文件大小为：数据文件94M，而日志文件为425M。

图一：数据库初始大小：

 

执行收缩操作：在数据库右键-任务-收缩-文件。在文件类型选择日志，点击确定，待执行完毕后。刷新【注意这里一定要刷新】查看文件数据库属性发现效果很不理想，只减少了19M。下图为收缩之后的效果：

图二：第一次收缩

于是就进行日志截断。日志截断可以使用命令行【BACKUP LOG WITH TRUNCATE_ONLY】来做。但是这样，就可能导致之前的操作信息丢失。不能恢复到以前的某个时间点。另外可以通过备份来自动截断日志，MSSQLServer中全备份是不会截断日志的，备份日志文件可以自动截断日志。接下来做日志备份：数据库右键-任务-备份-文件。备份类型选择事物日志，点击确定执行。

这样截断日志之后，再执行收缩操作后查看日志文件大小只有6M：

图三：截断之后收缩效果

附：日志收缩和日志截断的区别：

◆日志收缩
截断日志虽然确实从日志文件中清除了事务，但它并不会真正的减小物理日志文件的大小。SQL Server希望事务日志最终会扩展到其截断前的大小，所以截断不会释放已经分配给日志的硬盘空间。如果你的日志在某一时刻人为地扩展到某个大小，却再也无法恢复到这个大小的话可就麻烦大了。
◆日志截断 截断事务日志操作就是清除事务日志文件中的非活动记录。在一般的情况下，SQL Server能够自动执行截断操作，不需要人工干预管理。截断的频率取决于数据库的使用程度。你每进行一次完整恢复模式或大容量日志恢复模式的数据库备份，SQL Server就会截断一次事务日志。如果是在简单恢复模式下(不能还原事务日志)，SQL Server会在每个检查点之后截断事务日志。【注：全备份不能截断日志，必须备份日志文件之后才能截断日志，我在MSSQLServer2008中遇到的是这样的】
简单来说 日志收缩：物理的收缩数据日志文件的大小 日志截断：逻辑的释放日志空间以供事务日志重新使用