SQL SERVER事务日志已满详解

您所在的位置：网站首页 › 数据库事务日志满会导致什么 › SQL SERVER事务日志已满详解

SQL SERVER事务日志已满详解

2024-04-06 22:29| 来源: 网络整理| 查看: 265

恢复数据库其实可以不需要ldf的日志文件件，当附加MDF文件时，找不到日志文件时，直接删除，然后会自动重建的。错误描述：数据库的事务日志已满 (SQL Server Error 9002)。 ##千万不要直接进行日志文件收缩，除非你不需要考虑数据库的恢复文件。

事务日志文件（Transaction Log File）是用来记录数据库更新情况的文件，扩展名为ldf。当出现下面的情况时，就会报“事务日志已满”的错误：

日志文件没有设置了自动增长，当记录超过初始大小；日志文件设置了自动增长，“最大文件大小”设置了具体的大小（如2000M），当文件达到设置的大小时；日志文件设置了自动增长，最大文件大小”设置了“无限制”，当磁盘已满时；

如果出现这种情况标识的日志文件已满，那么当执行更新或者插入新记录时就会报错，此时如果通过系统视图 sys.databases 中的 log_reuse_wait_desc 列可以看到日志中的空间无法重用的原因。

SELECT log_reuse_wait , log_reuse_wait_desc from sys.databases where name='database name'

### 关于事务日志事务日志，用于记录所有事务以及每个事务对数据库所做的修改。如果系统出现故障，你将需要依靠该日志将数据库恢复到一致的状态。

每个事务都在事务日志中保留空间，以确保当出现由回滚语句或遇到错误引起的回滚时，有足够的日志空间。保留的空间量取决于在事务中执行的操作，但通常等于用于记录每个操作的空间量。事务完成后将释放此保留空间。

数据库的日志文件被分成多个虚拟日志文件（VLF），而VLF的状态有4种：

- active，示VLF中存在活动的事务（即未完成的事务）。- recoverable，表示VLF中的事务全部已经完成，但是某些操作（例如数据库镜像、复制等）还需要用到这些数据，因此不可以被覆盖。- reusable，表示VLF中的数据已经不需要了，可以被覆盖。- unused，表示VLF从未被使用。

我们可以执行下面的语句查看相关信息：

### 日志截断日志截断将释放日志文件的空间，以便由事务日志重新使用。必须定期截断事务日志，防止占满分配的空间。几个因素可能延迟日志截断，因此监视日志大小很重要。某些操作可以最小日志量进行记录以减少其对事务日志大小的影响。

日志截断从 SQL Server 数据库的逻辑事务日志中删除不活动的虚拟日志文件 (VLF)，，释放逻辑日志中的空间以便物理事务日志重用这些空间。如果事务日志从不截断，它最终将填满分配给物理日志文件的所有磁盘空间。

为了避免空间不足，除非由于某些原因延迟日志截断，否则将在以下事件后自动进行截断：

- 简单恢复模式下，在检查点之后发生。 - 在完整恢复模式或大容量日志恢复模式下，如果自上一次备份后生成检查点，则在日志备份后进行截断（除非是仅复制日志备份）。

日志截断并不减小物理日志文件的大小。若要减少物理日志文件的物理大小，则必须收缩日志文件。如果在日志收缩后还需要存储空间，则会再次增加事务日志，导致在增加日志操作期间产生性能开销。

在下面这些情况下，日志会自动截断：

系统设置日志恢复模型为简单。创建数据库以来从未进行过完全备份。曾经使用BACKUP LOG WITH NO_LOG / TRUNCATE_ONLY ### 备选解决方案

1. 备份日志（首选）

- 非简单恢复模式下，先备份数据库，然后执行备份日志 - 如果从未备份日志，则必须创建两个日志备份，以允许数据库引擎将日志截断到上次的备份点。 - 若要防止日志再次填满，请经常执行日志备份

2. 释放磁盘空间以便日志可以自动增长。

- 如果是因为磁盘已满导致的，最直接的就是想办法释放磁盘空间；

3. 将日志文件移到具有足够空间的磁盘驱动器。

4. 增加日志文件的大小。

5. 在其他磁盘上添加日志文件。

6. 完成或取消长时间运行的事务。

我们需要根据实际情况选择不同的解决方案。

### Simple 简单恢复模式

在简单恢复模式下，直接备份数据库，将会自动截断事务日志

### Full 完整恢复模式和大容量日志模式

一般情况下，在实际生产环境，我们都会完整恢复模式以保证数据的安全，最大限度的保证数据不丢失。在这个模式下，当数据文件丢失或损坏时，任然可以将数据恢复到任意时间点。

Full模式的旧称叫”Checkpoint without truncate log“，这时，当数据库进行完整备份后并不会截断事务日志，必须进行日志备份才会截断日志，否则事务日志文件会一直增大，直到到达设置最大限制或者撑爆硬盘，此时如果要更新数据则会报错（“事务日志已满”）。

###### 实际上，在非简单恢复模式下，都必须定期备份事务日志，使VLF变更为可用状态，释放逻辑日志中的空间。

### 备份日志

通过T-SQL进行日志备份如下：

-- 如果数据库没有执行过完整备份，需要先备份数据库 Backup Database [dbname] To disk=@dbBackupPath BACKUP LOG [dbname] TO DISK = @tranBackupPath1 -- 如果从未备份日志，则必须创建两个日志备份 BACKUP LOG [dbname] TO DISK = @tranBackupPath2

同样，也可以通过数据看管理工具（SQL Sever Management Studio）实现：

1. 备份数据库

2. 备份日志文件

### 长期解决方案

1. 数据库选项设置 “ 完整恢复模式（Full ）”；

或者执行下面语句：

USE master ALTER DATABASE dbname SET RECOVERY FULL

2. 每周完整备份数据库

Backup Database [dbname] To disk=@dbBackupPath

3. 每天差异备份数据库

BACKUP DATABASE [dbname] TO DISK=@dbBackupPathVar WITH DIFFERENTIAL;

4. 每30分钟备份事务日志（间隔时间根据实际需求，时间越短数据丢失几率越低）

BACKUP LOG [dbname] TO DISK = @tranBackupPath -- 关于选项 TRUNCATE_ONLY（已经在2005版废弃，2008版后不在支持），只截断不备份。 -- 选项 With NO_TRUNCATE，不截断日志，通常用于备份受损的数据库 -- WITH NO_LOG 已经废弃 ### 收缩日志文件

长时间没有备份日志文件，日志文件会变动越来越大，占用了大量的磁盘空间。当我们执行完日志截断后，文件占用的物理空间是不会变小的，如果想减小日志文件，我们就要收缩日志文件。

若要减少物理日志文件的物理大小，则必须收缩日志文件。知道事务日志文件包含未使用空间时，此方法很有用。仅当数据库处于联机状态，而且至少一个[虚拟日志文件 (VLF)](事务日志体系结构和管理指南 - SQL Server) 可用时，才能收缩日志文件。

可以执行下面语句进行事务日志收缩：

-- 注意：在SQL 2008中清除日志必须在简单模式下进行，等清除动作完毕在调回到完整模式（否则数据库就不支持时间点备份） ALTER DATABASE [dbname] SET RECOVERY SIMPLE WITH NO_WAIT --简单模式 USE [dbname] GO DBCC SHRINKFILE(N'dbnameFilename_log',11,TRUNCATEONLY) ALTER DATABASE [dbname] SET RECOVERY FULL WITH NO_WAIT --还原为完全模式

如果你不知道你的日志文件名称是什么，可以执行下面语句查看：

-- 获取数据文件列表，列name就是文件名称， --type=1代表是日记文件 SELECT name FROM sys.database_files WHERE type=1

你也可以通过管理工具操作实现：

1. 数据库设置成简单恢复模式

2. 收缩数据库日志文件

3. 改回完整恢复模式

### 关于日志文件大小设置

- 为避免因事务日志满而报错，建议开启事务日志的自动增长。 1. 文件增长（FILEGROWTH ）选项设置的事务日志的自动增长 (autogrow) 增量必须足够大，以领先于工作负载事务的需求。因此，为了避免经常向日志文件中扩充内容，应该采用足够大的文件增量。要正确设置事务日志的大小，建议监视以下时间内所占用的日志数量：- 执行完整备份所需的时间，因为日志备份在其完成后才能进行。- 最大型索引维护操作所需的时间。- 在数据库中执行最大批操作所需的时间。

2. 使用 FILEGROWTH 选项设置数据和日志文件的 autogrow 时，建议首选使用 size 而不是使用 percentage 进行设置，以便更好地控制增长比，因为 percentage 表示的是日益增长量。

- 如果不知道设置多少才适合自己的数据库，可以参考官方提供的分析脚本，点击查看：[事务日志设置大小分析脚本](microsoft/tigertoolbox)

执行脚本后的结果下图：

###### 我们可以根据分析结果进行相应设置。可以定期的执行分析，看看随着数据的增长，是否需要更改设置，以使得设置的更加合理。

### 注意事项

###### 千万不要直接进行日志文件收缩，除非你不需要考虑数据库的恢复文件。否则请先备份事务日志！在数据库中有多个日志文件不会以任何方式提升性能，因为事务日志文件不会像同一文件组中的数据文件一样使用[比例填充](页和区体系结构指南 - SQL Server)。日志文件可以设为自动收缩。但是，不建议这样做，auto_shrink 数据库属性默认设为 FALSE 。如果 auto_shrink 设置为 TRUE，则仅当其空间的 25% 以上未使用时，自动收缩才会减少文件的大小。

以下幾種方法均可：

1.清空日志DUMP TRANSACTION 库名 WITH NO_LOG

2.收缩数据库文件(如果不压缩,数据库的文件不会减小企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件 --选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了 --选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了先提供一种复杂的方法压缩日志及数据库文件如下： 1.清空日志　 DUMP TRANSACTION 库名 WITH NO_LOG 2.截断事务日志：　 BACKUP LOG 数据库名 WITH NO_LOG3.收缩数据库文件(如果不压缩,数据库的文件不会减小　企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件　 --选择日志文件--在收缩方式里选择收缩至天天上网M,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了　 --选择数据文件--在收缩方式里选择收缩至天天上网M,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了　也可以用SQL语句来完成　 --收缩数据库　 DBCC SHRINKDATABASE(客户资料)　 --收缩指定数据文件,1是文件号,可以通过这个语句查询到:select * from sysfiles　 DBCC SHRINKFILE(1)

更简单的方法：1。右建数据库属性窗口--故障还原模型--设为简单2。右建数据库所有任务--收缩数据库3。右建数据库属性窗口--故障还原模型--设为大容量日志记录————————————————版权声明：本文为CSDN博主「eley」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/loeley/article/details/6857858

【本文地址】

SQL SERVER事务日志已满详解

SQL SERVER事务日志已满详解

今日新闻

推荐新闻