当前位置:Gxlcms > mysql > SQLServer2008事务日志物理文件过大无法缩小分析

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

时间:2021-07-01 10:21:17 帮助过:34人阅读

前段时间在做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. 数据库复制或者镜像出了异常。

要避免日志文件不停增长,其实就是要避免上面这些情况的发生。对于一个最近不会去做日志备份的数据库,设成简单恢复模式即可。如果数据库设成了完整恢复模式,那就一定要安排定期做日志备份。如复制或镜像任务出问题,要及时解决。如果没办法解决,就必须暂时拆除复制或镜像,以防止日志记录越积越多,最终造成数据库不可使用。在设计程序的时候,也要避免事务时间过长,一个事务做太多的操作。数据库晚上或周末会做一些维护工作,例如历史数据清洗整理,数据导入导出,索引重建等。这些操作都可能写许多日志,所以要为它们预留出足够的空间,并且在做完之后及时备份。

linux

人气教程排行