现象:一个数据库,数据文件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 | 事务处于活动状态(所有恢复模式)。 |
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)