SQL SERVER事务日志已满详解

错误描述：数据库的事务日志已满 (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'

执行上面的语句得到的结果：

log_resuse_wait_desc.jpg

关于事务日志

事务日志，用于记录所有事务以及每个事务对数据库所做的修改。 如果系统出现故障，你将需要依靠该日志将数据库恢复到一致的状态。
每个事务都在事务日志中保留空间，以确保当出现由回滚语句或遇到错误引起的回滚时，有足够的日志空间。 保留的空间量取决于在事务中执行的操作，但通常等于用于记录每个操作的空间量。 事务完成后将释放此保留空间。

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

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

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

DBCC loginfo

查询结果如下图：

loginfo.jpg

Status列就是VLF的状态，0代表reusable或者unused，2则代表active或者recoverable；只有处于reusable和unused状态时，VLF才可以通过日志截断来释放空间。

日志截断

日志截断将释放日志文件的空间，以便由事务日志重新使用。 必须定期截断事务日志，防止占满分配的空间。 几个因素可能延迟日志截断，因此监视日志大小很重要。 某些操作可以最小日志量进行记录以减少其对事务日志大小的影响。
日志截断从 SQL Server 数据库的逻辑事务日志中删除不活动的虚拟日志文件 (VLF)，，释放逻辑日志中的空间以便物理事务日志重用这些空间。 如果事务日志从不截断，它最终将填满分配给物理日志文件的所有磁盘空间。

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

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

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

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

1. 系统设置日志恢复模型为简单。
2. 创建数据库以来从未进行过完全备份。
3. 曾经使用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

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

1. 备份数据库

   
   
   backupdb1.jpg

backupdb2.jpg

2. 备份日志文件

   
   
   backup log.jpg

长期解决方案

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

   
   
   db_option.jpg
   
   

   或者执行下面语句：

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 2008中清除日志必须在简单模式下进行，等清除动作完毕在调回到完整模式（否则数据库就不支持时间点备份）

ALTER DATABASE [dbname] SET RECOVERY SIMPLE WITH NO_WAIT    --简单模式
USE [dbname]
GO 
DBCC SHRINKFILE(N'dbnameFilename_log',11,TRUNCATEONLY)
ALTER DATABASE [56kong] SET RECOVERY FULL WITH NO_WAIT      --还原为完全模式

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

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

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

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

   
   
   simple mode1.jpg
   
   
   simple mode2.jpg

2. 收缩数据库日志文件

   
   
   shrink01.jpg

shrink02.jpg

3. 改回完整恢复模式

   
   
   full01.jpg

关于日志文件大小设置

• 为避免因事务日志满而报错，建议开启事务日志的自动增长。

1. 文件增长（FILEGROWTH ）选项设置的事务日志的自动增长 (autogrow) 增量必须足够大，以领先于工作负载事务的需求。 因此，为了避免经常向日志文件中扩充内容，应该采用足够大的文件增量。 要正确设置事务日志的大小，建议监视以下时间内所占用的日志数量：

• 执行完整备份所需的时间，因为日志备份在其完成后才能进行。
• 最大型索引维护操作所需的时间。
• 在数据库中执行最大批操作所需的时间。

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

• 如果不知道设置多少才适合自己的数据库，可以参考官方提供的分析脚本，点击查看：事务日志设置大小分析脚本
  执行脚本后的结果下图：
  
  logfile_Setting.jpg

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

注意事项

• 千万不要直接进行日志文件收缩，除非你不需要考虑数据库的恢复文件。否则请先备份事务日志！

• 在数据库中有多个日志文件不会以任何方式提升性能，因为事务日志文件不会像同一文件组中的数据文件一样使用比例填充。
• 日志文件可以设为自动收缩。 但是，不建议这样做，auto_shrink 数据库属性默认设为 FALSE 。 如果 auto_shrink 设置为 TRUE，则仅当其空间的 25% 以上未使用时，自动收缩才会减少文件的大小。

参考文件:

• 解决事务日志已满的问题（SQL Server 错误 9002）
• Log Reuse Waits Explained: LOG_BACKUP
• SQL Server 2008/2012 完整数据库备份+差异备份+事务日志备份 数据库备份
• 管理事务日志文件的大小