解决SQLServer事物日志过大的方法

在平时的运维中，DBA会经常遇到SQL Server日志文件过大导致磁盘空间不足的情况，这时候就需要把日志文件收缩一下来释放磁盘空间，下面介绍了收缩日志文件的方法

一．要进行日志文件收缩首先要把事物日志截断。

正常情况下SQL Server的事物日志自动截断方法分以下两种情况

简单恢复模式下，在检查点之后发生。
在完整恢复模式或大容量日志恢复模式下，如果自上一次备份后生成检查点，则在日志备份后进行截断（除非是仅复制日志备份）。

查看恢复模式的方法如下
在命令下查看

语法：SELECT name, recovery_model_desc
   FROM sys.databases
      WHERE name = '数据库名' ;

查看效果如图所示

如果是在完整或者大容量日志的恢复模式下，那我们就要查看一下近期是否做过完整的日志以确定日志是否被自动截断过
关于数据库的检查点，检查点会根据系统的恢复间隔自动生成检查点
有时候因素导致事物日志截断会被延迟,我们可以使用命令来查看截断被延迟的原因

命令语法：SELECT name,log_reuse_wait,log_reuse_wait_desc
   FROM sys.databases
      WHERE name = '数据库名' ;

我们可以根据结果中输出的log_reuse_wait 和 log_reuse_wait_desc 的值来查找原因

下表为log_reuse_wait 和 log_reuse_wait_desc 的值的解释
此表来源于微软知识库：http://technet.microsoft.com/zh-cn/library/ms345414(v=sql.90).aspx

二．收缩日志文件

在事物日志进行截断之后，那我们就可以把日志文件进行收缩
使用命令进行收缩
下图是把以下示例将 test用户数据库中名为test_log的数据文件的大小收缩到 10 MB。

执行完命令后查看日志文件是否缩小，日志文件收缩后不会比虚拟日志文件小

通常情况下，日志没有被收缩都是因为日志文件未被截断造成的。还有一个办法是把将数据库恢复模式设置为“ SIMPLE ”模式后再次运行 DBCC SHRINKFILE 命令更改数据库恢复模式的命令如下

ALTER DATABASE 数据库名 SET RECOVERY  FULL  ; --更改为完全模式
ALTER DATABASE 数据库名 SET RECOVERY  bulk_logged ; --更改为大容量日志模式
ALTER DATABASE数据库名SET RECOVERY  ; --更改为简单模式

执行效果如图所示

注意事项：如果更改为简单模式，那以后将无需事物日志备份，但是一定要确保的定期执行数据库备份来确保数据的完整性。
如果更改为完整模式或者大容量日志模式，那一定首先进行一次完整的数据库备份以启动日志链，并且设置任务计划来定期的日志备份

有关日志收缩的操作差不多就介绍到这了