SQL Server 2008事务日志收缩后物理文件大小无法减小的解决办法

用图形方式对SQL Server 2008日志文件收缩后显示成功，但文件的物理大小支始终没有变化。有时候做一下备份后再收缩会收缩成功，但有时候又不起作用。查询资料后发现需做以下处理：

查看日志信息 

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

1 DBCC LOGINFO('数据库名称') 

 我们看到status=0的日志，代表已经备份到磁盘的日志文件；而status=2的日志还没有备份。当我们收缩日志文件时，收缩掉的空间其实就是status=0的空间，如果日志物理文件无法减小，这里一定能看到非常多status=2的记录。接下来分析为什么会有这么多status=2 的记录

查看日志截断延迟原因

活跃(active)的日志无法通过收缩来截断，有各种原因会使日志截断延迟，具体表现就是事务日志的物理文件无法通过截断、收缩来减小，通过下面的代码可以看到实例上每个数据库的日志截断延迟原因：

1 USE [master]
2 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	正在进行日志扫描（所有恢复模式）。 这是日志截断延迟的常见原因，通常也是主要原因。

大部分情况下是没有备份的问题，用代码方式重新备份后即可解决。

 

如果是REPLICATION则可通过sp_replcmds或sp_repltrans查询是否有挂起的复制。如下图所示：

执行以下语句可清除挂起的复制。

EXEC sp_repldone @xactid = null, @xact_segno = null, @numtrans = 0,    @time = 0, @reset = 1

 

参考资料：

http://www.yesky.com/imagesnew/software/tsql/ts_sp_repl3_7ug5.htm

http://msdn.microsoft.com/zh-cn/library/ms345414.aspx

原文地址:  http://blog.sina.com.cn/s/blog_557194c3010106xw.html