SQL Server 2008、2012事务日志截断(truncate)与收缩(shrink)

现象：一个数据库，数据文件10+G ，事务日志达20+G，而且使用常规的截断、收缩方法均无法减小日志物理文件的尺寸

解决方法：

步骤一：

查看日志信息

在查询分析器中执行如下代码来查看日志信息：

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

事务处于活动状态（所有恢复模式）。

一个长时间运行的事务可能存在于日志备份的开头。在这种情况下，可能需要进行另一个日志备份才能释放空间。

事务被延迟（仅适用于 SQL Server 2005 Enterprise Edition 及更高版本）。“延迟的事务” 是有效的活动事务，因为某些资源不可用，其回滚受阻。

DATABASE_MIRRORING

数据库镜像暂停，或者在高性能模式下，镜像数据库明显滞后于主体数据库（仅限于完整恢复模式）。

REPLICATION

在事务复制过程中，与发布相关的事务仍未传递到分发数据库（仅限于完整恢复模式）。

DATABASE_SNAPSHOT_CREATION

正在创建数据库快照（所有恢复模式）。

这是日志截断延迟的常见原因，通常也是主要原因。

LOG_SCAN

正在进行日志扫描（所有恢复模式）。

这是日志截断延迟的常见原因，通常也是主要原因。

针对延迟日志截断原因的部分解决方案

LOG_BACKUP

备份日志后再执行收缩即可

REPLICATION

这是我遇到的情况，但我根本没有启用过REPLICATION，据查，这好像是SQLSERVER2008的一个BUG，解决方法是给标有“REPLICATION”的数据库任意一个表创建数据库事务复制（TRANSACTION REPLICATION），然后再删除，执行数据库与日志备份后，就可以收缩了。

小技巧：

1：USE testDB
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

2:DBCC SQLPERF(LOGSPACE)

3:USE [master]
 SELECT [name] ,[database_id] ,[log_reuse_wait] ,[log_reuse_wait_desc] FROM [sys].[databases]

4： DBCC SHRINKFILE (N'testDB_log' ,1024, TRUNCATEONLY)