SQLServer 2008中事务日志已满问题处理

来源：这里教程网时间：2026-03-02 11:51:33 作者：

# 错误描述
错误描述：数据库的事务日志已满。若要查明无法重用日志中的空间的原因 ，请参阅sys.databases 中的 log_reuse_wait_desc 列 。
# 错误原因
## 事务日志介绍
官方文档中有如下说明：
>SQL Server 数据库引擎 事务日志的特征：
事务日志是作为数据库中的单独的文件或一组文件实现的。 日志缓存与数据页的缓冲区高速缓存是分开管理的，因此可在 SQL Server 数据库引擎中生成简单、快速和功能强大的代码。 有关详细信息，请参阅事务日志物理体系结构。
日志记录和页的格式不必遵守数据页的格式。
事务日志可以在几个文件上实现。 通过设置日志的 FILEGROWTH 值可以将这些文件定义为自动扩展。 这样可减少事务日志内空间不足的可能性，同时减少管理开销。 有关详细信息，请参阅 ALTER DATABASE (Transact-SQL) 文件和文件组选项。
重用日志文件中空间的机制速度快且对事务吞吐量影响最小。
>日志截断将释放日志文件的空间，以便由事务日志重新使用。 必须定期截断事务日志，防止占满分配的空间。 几个因素可能延迟日志截断，因此监视日志大小很重要。 某些操作可以最小日志量进行记录以减少其对事务日志大小的影响。
日志截断从 SQL Server 数据库的逻辑事务日志中删除不活动的虚拟日志文件 (VLF)，释放逻辑日志中的空间以便物理事务日志重用这些空间。 如果事务日志从不截断，它最终将填满分配给物理日志文件的所有磁盘空间。
为了避免空间不足，除非由于某些原因延迟日志截断，否则将在以下事件后自动进行截断：
简单恢复模式下，在检查点之后发生。
在完整恢复模式或大容量日志恢复模式下，如果自上一次备份后生成检查点，则在日志备份后进行截断（除非是仅复制日志备份）。
> 日志截断并不减小物理日志文件的大小。 若要减少物理日志文件的物理大小，则必须收缩日志文件。 有关收缩物理日志文件大小的信息，请参阅 管理事务日志文件的大小。但是，请记住可能延迟日志截断的因素。 如果在日志收缩后还需要存储空间，则会再次增加事务日志，导致在增加日志操作期间产生性能开销。
在日志记录长时间处于活动状态时，事务日志截断将延迟，事务日志可能填满。
实际上，日志截断会由于多种原因发生延迟。查询 sys.databases 目录视图的 log_reuse_wait 和 log_reuse_wait_desc 列，了解哪些因素（如果存在）阻止日志截断。 下表对这些列的值进行了说明。
		
| log_reuse_wait 值 | log_reuse_wait_desc 值 | 描述 
|--|--|:----------
| 0 | NOTHING | 当前有一个或多个可重复使用的虚拟日志文件 (VLF)。
|1	|CHECKPOINT |	自上次日志截断之后，尚未生成检查点，或者日志头尚未跨一个虚拟日志 (VLF) 文件移动。 （所有恢复模式）这是日志截断延迟的常见原因。 有关详细信息，请参阅数据库检查点 (SQL Server)。
|2	|LOG_BACKUP	|在截断事务日志前，需要进行日志备份。 （仅限完整恢复模式或大容量日志恢复模式）完成下一个日志备份后，一些日志空间可能变为可重复使用。
|3	|ACTIVE_BACKUP_OR_RESTORE	|数据备份或还原正在进行（所有恢复模式）。如果数据备份阻止了日志截断，则取消备份操作可能有助于解决备份直接导致的此问题。
|4	|ACTIVE_TRANSACTION	|事务处于活动状态（所有恢复模式）：一个长时间运行的事务可能存在于日志备份的开头。 在这种情况下，可能需要进行另一个日志备份才能释放空间。 请注意，长时间运行的事务将阻止所有恢复模式下的日志截断，包括简单恢复模式，在该模式下事务日志一般在每个自动检查点截断。延迟事务。 “延迟的事务 ”是有效的活动事务，因为某些资源不可用，其回滚受阻。 有关导致事务延迟的原因以及如何使它们摆脱延迟状态的信息，请参阅延迟的事务 (SQL Server)。长时间运行的事务也可能会填满 tempdb 的事务日志。 Tempdb 由用户事务隐式用于内部对象，例如用于排序的工作表、用于哈希的工作文件、游标工作表，以及行版本控制。 即使用户事务只包括读取数据（SELECT 查询），也可能会以用户事务的名义创建和使用内部对象， 然后就会填充 tempdb 事务日志。
|5	|DATABASE_MIRRORING	|数据库镜像暂停，或者在高性能模式下，镜像数据库明显滞后于主体数据库。 （仅限完整恢复模式）有关详细信息，请参阅数据库镜像 (SQL Server)。
|6	|REPLICATION	|在事务复制过程中，与发布相关的事务仍未传递到分发数据库。 （仅限完整恢复模式）有关事务复制的信息，请参阅 SQL Server Replication。
|7	|DATABASE_SNAPSHOT_CREATION	|正在创建数据库快照。 （所有恢复模式）这是日志截断延迟的常见原因，通常也是主要原因。
|8	|LOG_SCAN	|发生日志扫描。 （所有恢复模式）这是日志截断延迟的常见原因，通常也是主要原因。
|9	|AVAILABILITY_REPLICA|	可用性组的辅助副本正将此数据库的事务日志记录应用到相应的辅助数据库。 （完整恢复模式）有关详细信息，请参阅： AlwaysOn 可用性组概述 (SQL Server)。
|10	|—	|仅供内部使用
|11	|—	|仅供内部使用
|12	|—	|仅供内部使用
|13	|OLDEST_PAGE|	如果将数据库配置为使用间接检查点，数据库中最早的页可能比检查点日志序列号 (LSN) 早。 在这种情况下，最早的页可以延迟日志截断。 （所有恢复模式）有关间接检查点的信息，请参阅数据库检查点 (SQL Server)。
|14	|OTHER_TRANSIENT	|当前未使用此值。
## 排查日志已满问题
官方文档：[解决事务日志已满的问题（SQL Server 错误 9002）](https://docs.microsoft.com/zh-cn/sql/relational-databases/logs/troubleshoot-a-full-transaction-log-sql-server-error-9002?view=sql-server-2017)

# 解决方法
在SQL 2008中清除日志必须在简单模式下进行，等清除动作完毕在调回到完整模式（否则数据库就不支持时间点备份）
```sql
--查看日志文件状况
 use dbname
 dbcc shrinkfile('logname')  --like  XXXX_log
 ```
## 方法一：界面方式收缩日志
### 第一步：调整恢复模式
选择数据库-属性-选项-恢复模式-选择简单
![在这里插入图片描述](https://img-blog.csdn.net/20181007092451207?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1OTIzNTA=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70)
![在这里插入图片描述](https://img-blog.csdn.net/20181007092511682?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1OTIzNTA=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70)
>在完整 恢复模式下，所有大容量操作都将被完整地记录下来。 但是，可以通过将数据库暂时切换到用于大容量操作的大容量日志恢复模式，最小化一组大容量操作的日志记录。 最小日志记录比完整日志记录更为有效，并在大容量事务期间，降低了大规模大容量操作填满可用的事务日志空间的可能性。 不过，如果在最小日志记录生效时数据库损坏或丢失，则无法将数据库恢复到故障点。
### 第二步：收缩日志文件
选择数据库-任务-收缩-文件
![在这里插入图片描述](https://img-blog.csdn.net/20181007092531672?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1OTIzNTA=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70)
![在这里插入图片描述](https://img-blog.csdn.net/20181007092547477?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1OTIzNTA=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70)
#### 选项说明
**"数据库"**
显示所选数据库的名称。
**"文件类型"**
选择文件的文件类型。 可用的选项包括 “数据” 和 “日志” 文件。 默认选项为 “数据”。 选择不同的文件组类型，其他字段中的选项会相应地发生更改。
**"文件组"**
在与以上所选的 “文件类型” 相关联的文件组列表中选择文件组。 选择不同的文件组，其他字段中的选项会相应地发生更改。
**"文件名"**
从所选文件组和文件类型的可用文件列表中选择文件。
**"位置"**
显示当前所选文件的完整路径。 此路径无法编辑，但是可以复制到剪贴板。
**"当前分配的空间"**
对于数据文件，会显示当前分配的空间。 对于日志文件，会显示根据 DBCC SQLPERF (LOGSPACE) 的输出计算出的当前分配的空间。
**"可用空间"**
对于数据文件，会显示根据 SHOWFILESTATS (fileid) 的输出计算出的当前可用空间。 对于日志文件，会显示根据 DBCC SQLPERF (LOGSPACE) 的输出计算出的当前可用空间。
**"释放未使用的空间"**
将任何文件中未使用的空间释放给操作系统，并将文件收缩到最后分配的区，因此无需移动任何数据即可减小文件尺寸。 不会将行重新定位到未分配的页。
**"在释放未使用的空间前重新组织页"**
等效于执行用于指定目标文件大小的 DBCC SHRINKFILE。 选中此选项时，用户必须在 “将文件收缩到” 框中指定目标文件的大小。
**"将文件收缩到"**
为收缩操作指定目标文件的大小。 此大小值不得小于当前分配的空间或大于为文件分配的全部区的大小。 如果输入的值超出最小值或最大值，那么一旦焦点改变或单击工具栏上的按钮时，数值将恢复到最小值或最大值。
**"通过将数据迁移到同一文件组中的其他文件来清空文件"**
从指定文件迁移所有数据。 此选项允许使用 ALTER DATABASE 语句删除文件。 此选项等效于执行带有 EMPTYFILE 选项的 DBCC SHRINKFILE。
### 第三步：调整恢复模式
选择数据库-属性-选项-恢复模式-选择简单
![在这里插入图片描述](https://img-blog.csdn.net/20181007092602435?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1OTIzNTA=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70)
## 方法二：命令行方式收缩日志
**本方法未实际操作**
```sql
USE [master]
	GO 
	ALTER DATABASE DNName SET RECOVERY SIMPLE WITH NO_WAIT
	GO
	ALTER DATABASE DNName SET RECOVERY SIMPLE--简单模式
	GO
	USE DNName
	GO
	DBCC SHRINKFILE(N'DNName_Log',11,TRUNCATEONLY)
	GO
	USE [master]
	GO
	ALTER DATABASE DNName SET RECOVERY FULL WITH NO_WAIT
	GO
	ALTER DATABASE DNName SET RECOVERY FULL--还原为完全模式
	GO
```
# 另请参阅
[SQL Server 事务日志体系结构和管理指南](https://docs.microsoft.com/zh-cn/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-2017)
[管理事务日志文件的大小](https://docs.microsoft.com/zh-cn/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-2017)
[事务日志](https://docs.microsoft.com/zh-cn/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-2017)
[收缩文件](https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/shrink-a-file?view=sql-server-2017)
[探查 SQL Server 虚拟日志文件](http://blog.51cto.com/jimshu/1174474)
[SQL2008R2 收缩数据库问题 - 日志文件不变小](https://www.cnblogs.com/strayromeo/p/6961758.html)