SQL Server 2008 事务日志物理文件过大无法缩小 分析

前段时间在做SQL Server 2008数据库维护时，遇到 日志无法收缩。最后和同事一起分析了下原因。将原因做了一个汇总。现将 分析结果分享一下。以共享有相同情况而又未解决的同仁们参考。

查看日志信息  
 dBCC LOGINFO('数据库名') 
 
  我们看到status=0的日志，代表已经备份到磁盘的日志文件；而status=2的日志还没有备份。当我们收缩日志文件时，收缩掉的空间其实就是status=0的空间，如果日志物理文件无法减小，这里一定能看到非常多status=2的记录。 
 
  活跃(active)的日志无法通过收缩来截断，有各种原因会使日志截断延迟，具体表现就是事务日志的物理文件无法通过截断、收缩来减小，通过下面的代码可以看到实例上每个数据库的日志截断延迟原因。 
USE [master] 
SELECT [name] ,[database_id] ,[log_reuse_wait] ,[log_reuse_wait_desc] FROM [sys].[databases]  
 
常见状态： 
log_reuse_wait_desc 值   说明 
NOTHING 当前有一个或多个可重复使用的虚拟日志文件。 
CHECKPOINT  自上次日志截断之后，尚未出现检查点，或者日志头部尚未跨一个虚拟日志文件移动（所有恢复模式）。 
这是日志截断延迟的常见原因。 
LOG_BACKUP  需要日志备份，以将日志的头部前移（仅适用于完整恢复模式或大容量日志恢复模式）。 
 
ACTIVE_BACKUP_OR_RESTORE    数据备份或还原正在进行（所有恢复模式）。 
ACTIVE_TRANSACTION  事务处于活动状态（所有恢复模式）。 
 
DATABASE_MIRRORING  数据库镜像暂停，或者在高性能模式下，镜像数据库明显滞后于主体数据库（仅限于完整恢复模式）。 
 
REPLICATION 在事务复制过程中，与发布相关的事务仍未传递到分发数据库（仅限于完整恢复模式）。 
sp_removedbreplication 'db_test' 
 
DATABASE_SNAPSHOT_CREATION  正在创建数据库快照（所有恢复模式）。 
 
LOG_SCAN    正在进行日志扫描（所有恢复模式）。 
 
OTHER_TRANSIENT 
    此值当前未使用  
 
日志文件增长的原因 
 
SQL Server会为所有的修改记录日志，以便将来重新提交或者回滚时使用。那不停地记录，日志文件岂不会空间耗尽？为此，SQL Server设计了相对应的机制，能够定期清理日志文件中不再需要的日志记录。 
 
1. 所有没有经过"检查点"的日志记录。 
 
SQL Server定期做检查点（Checkpoint），保证所有的"脏页"都被写入硬盘。未做检查点的修改，可能仅是内存中的修改。数据文件里还没有同步。SQL Server要硬盘上的日志文件里有一份记录，以便在异常重启后重新修改。 
 
2. 所有没有提交的事务所产生的日志记录，以及在它们之后的所有日志记录。 
 
如果一个事务还没有提交，那它可以在任何时候回滚。SQL Server必须做好这种准备，以便能够从日志记录中找回修改前的数据内容，完成回滚。在SQL Server里面，所有的日志记录都有严格顺序，中间不可以有任何跳跃。所以如果某个数据库有没有提交的事务，SQL Server会标记所有从这个事务开始的日志记录（不管和这个事务有没有关系）为活动事务日志。这些日志记录都有可能"需要"被用来做回滚。 
 
3. 所有要做备份的日志记录。 
 
如果数据库设的恢复模式不是简单模式，那SQL Server就假设用户是要去备份日志记录的。所有未被备份的记录，SQL Server都会为用户保留，哪怕这些记录对数据库本身已经没有其他用途了。 
 
4. 有其他需要读取日志的数据库功能模块。 
 
除了数据库引擎，还有一些功能，比如说，事务型复制（Transactional Replication）和数据库镜像（Database Mirroring）也需要读取日志文件中的内容，完成它们的同步工作。在这些功能组件没有读取日志记录之前，SQL Server也会保留。 
 
对所有"不需要"的日志记录，SQL Server会在每个检查点做一次截断的动作，把这些记录占用的空间标志成可重用。这样这些空间就被释放出来。因为日志文件是循环使用的，只要日志文件里有这样的空间，SQL Server都会去重用，所以不会报告空间已满，或者试图去做自动增长。SQL Server做检查点的频率取决于服务器属性"Recovery Interval"。默认大概一分钟左右做一次检查点。 
 
 
如果日志文件里"需要"的记录越来越多，那就会出现日志文件不停增长的现象。通常的原因有下面几个。 
 
1. 数据库恢复模式不是简单模式，但是没有安排日志备份。 
 
对于非简单模式的数据库，只有做完日志备份后记录才会被截断。做完整备份和差异备份都不会起这个作用。 
 
2. 数据库上面有一个很长时间都没有提交的事务。 
 
由于应用程序设计的问题，有些连接可能会遗留一个事务在SQL Server里面，而不是及时提交了它。SQL Server是不会干预用户的这种行为的。只要这个连接不退出，这个事务就会永远存在，直到客户端主动提交或者回滚它。而从这个事务开启的那个时间点开始的所有日志记录，SQL Server都会保留。（做过日志备份也没有用。） 
 
3. 数据库上有一个很大的事务正在运行。 
 
例如，某个用户正在建立/重建索引，或者用DELETE/INSERT语句删除或插入大量数据等。或者用户端开了一个服务器端游标，但是没有把数据及时取走等。 
 
4. 数据库复制或者镜像出了异常。 
 
要避免日志文件不停增长，其实就是要避免上面这些情况的发生。对于一个最近不会去做日志备份的数据库，设成简单恢复模式即可。如果数据库设成了完整恢复模式，那就一定要安排定期做日志备份。如复制或镜像任务出问题，要及时解决。如果没办法解决，就必须暂时拆除复制或镜像，以防止日志记录越积越多，最终造成数据库不可使用。在设计程序的时候，也要避免事务时间过长，一个事务做太多的操作。数据库晚上或周末会做一些维护工作，例如历史数据清洗整理，数据导入导出，索引重建等。这些操作都可能写许多日志，所以要为它们预留出足够的空间，并且在做完之后及时备份。