当前位置:Gxlcms > mysql > 日志文件持续增长的案例

日志文件持续增长的案例

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

在完整恢复模式时,只有执行事务日志备份之后日志才被截断。 在日志被截断之前,有一种特殊情形导致数据文件的空间几乎没有变化,而日志文件却持续增长。一、搭

  在完整恢复模式时,只有执行事务日志备份之后日志才被截断。

  在日志被截断之前,有一种特殊情形导致数据文件的空间几乎没有变化,而日志文件却持续增长。


一、搭建测试环境

1. 创建数据库

CREATE DATABASE [db01]

ON PRIMARY

( NAME = N'db01', FILENAME = N'C:\sqldata\db01.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'db01_log', FILENAME = N'C:\sqldata\db01_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB)

ALTER DATABASE [db01] SET RECOVERY FULL


2. 创建表并插入一条新记录

USE db01

CREATE TABLE table1

(UserID int,pwd char(20),OtherInfo char(4000),modifydate datetime)

INSERT table1

VALUES ( 123,'456','this is the first record',getdate() )


3. 再插入999条记录

DECLARE @i int

SET @i=0

WHILE @i<999

BEGIN

INSERT INTO table1

SELECT cast(floor(rand()*100000) as int), cast(floor(rand()*100000) as varchar(20)), cast(floor(rand()*100000) as char(4000)), GETDATE()

SET @i=@i+1

END


4. 查看数据库的磁盘使用空间

  此时,mdf文件为7MB,ldf文件为1MB。使用情况如下图。


183801944.png



二、第一次测试

1. 重复修改第一条记录

DECLARE @i int

SET @i=0

WHILE @i<1000

BEGIN

UPDATE table1

SET pwd = cast(floor(rand()*100000) as varchar(20)),

OtherInfo=cast(floor(rand()*100000) as char(4000)),

modifydate = GETDATE()

WHERE UserID=123

SET @i=@i+1

END


2. 查看数据库的磁盘空间

  查看数据库的磁盘空间,结果发现:mdf文件和ldf文件的磁盘空间都几乎没有变化。


3、原因分析

  按照我们的理解:在完整恢复模式时,事务日志应当记录table1的所有数据变化的LSN记录,然后可以通过重做LSN从而恢复到任意时间点。

  既然我们已经对table1的第1条记录反复修改了1000次,理论上应当在ldf文件中记录这些修改的LSN记录。可是上面的测试结果显示ldf文件的磁盘空间没有变化,说明ldf文件中没有保存所有的LSN记录。这是为什么呢?

  回到事务日志的工作机制上,事务日志备份的恢复目标是什么呢?首先需要基于一个完整备份,然后从这个完整备份的最后一个LSN开始依次重做所有的LSN记录,从而将数据恢复直到最后一笔交易。

  要注意到,数据库db01自创建以来,没有做过完整备份。原因终于找到了!由于没有完整备份,数据库引擎认为ldf没有必要保存这些LSN记录(VLF被自动复用),因为这些LSN记录不能用于恢复。


三、第2次测试

1. 完整备份

BACKUP DATABASE [db01]

TO DISK = N'C:\Backup\db01.bak' WITH FORMAT, INIT


2. 重复修改第1条记录

  再重复修改第1条记录,反复修改1000次。


3. 查看磁盘空间

  查看数据库的磁盘空间,结果发现:mdf文件仍然为7MB,而ldf文件从1MB增长到18MB。当然,如果反复修改的次数更多,将会使ldf增长更多。


192244734.png


4. 原因分析

  如果前面没有做过事务日志备份,则ldf文件中保存着自上一次完整备份以来的所有LSN记录;如果前面有做过事务日志备份,则ldf文件中保存着自上一次事务日志备份以来的所有LSN记录。

  mdf文件没有增长,是因为数据变化始终发生在第一个数据行,,始终用新的数据去覆盖旧的数据,因此不需要新的磁盘空间。而ldf文件持续增长,是因为需要新的磁盘空间用来保存所有的LSN记录。



四、场景示例

  例如,某个table记录了用户最后登入某个应用系统的时间。当无数个用户频繁登入系统,即使这些用户登入应用系统后没有任何操作就退出了系统,最后导致mdf文件几乎不变而ldf文件却持续增长。



五、收缩日志文件

  请参考 《如何收缩事务日志》


本文出自 “我们一起追过的MSSQL” 博客,请务必保留此出处

人气教程排行