记一次SQL server2008 数据库事务日志已满，导致程序崩溃排查过程

纸上得来终觉浅，绝知此事要躬行。嗨，大家好！我是码农刚子。

现象：所有功能界面操作长时间无响应，提示：数据库'ERPData'的事务日志已满。若要查明无法重用日志中的空间的原因，请參阅sys.databases 中的 log_reuse_wait_desc 列。
首先检查了服务器的磁盘空间和内存使用量，空间都是足够的。接下来分析错误提示。
看到这个错误提示，说明你的SQL Server 2008数据库“ERPData”的事务日志已经满了，新的事务无法写入。错误信息里其实已经给出了排查思路：重点查看 sys.databases 中的 log_reuse_wait_desc 列，它能告诉你日志空间为什么无法被重用。
第一步：执行诊断SQL，定位根本原因

打开SQL Server Management Studio (SSMS) 登录并连接到对应的实例，并运行以下查询：

1. SELECT [name] ,[database_id] ,[log_reuse_wait_desc] FROM [sys].[databases]WHERE [name] = 'ERPData';

复制代码

重点关注 log_reuse_wait_desc 列返回的值，它将直接指示问题所在。
第二步：解读查询结果与解决方案

查询会返回一个状态值，以下是针对 SQL Server 2008 最常见的几种状态及其含义和解决办法：

log_reuse_wait_desc 值	含义解释	推荐解决方案
NOTHING	这通常意味着当前就有可重用的空间，但日志文件可能因为初始设置过大或之前未收缩而占据了大量磁盘空间。	你可以直接进行日志收缩来释放空间给操作系统。可以参考下文“通用解决步骤”中的方法。
LOG_BACKUP	这是最常见的原因。数据库处于完整恢复模式，但你从未或很久没有备份事务日志了。日志文件在备份之前不会自动截断，导致空间持续增长直至占满。	立即执行一次事务日志备份。备份完成后，日志中不活动的部分就会被截断，空间就能被重用。备份语句示例：BACKUP LOG [数据库名称] TO DISK = 'D:\backup\data1_log_backup.trn'
ACTIVE_TRANSACTION	有一个长时间运行且未提交的事务。这可能是一个未提交的BEGIN TRAN ，或者一个正在回滚的大型事务。从该事务开始后的所有日志记录都必须保留，因此日志无法截断。	需要找到并处理这个“活跃事务”。你可以使用 DBCC OPENTRAN ('数据库名称') 命令来查看最早的一个活动事务的信息。根据结果，要么提交它（COMMIT ），要么回滚它（ROLLBACK ），或者联系相关应用开发者。
CHECKPOINT	自上次日志截断后，还没有发生检查点（Checkpoint），这通常是暂时性的。在活动量大的数据库中偶尔出现。	通常等待系统自动发出检查点即可，一般不会持续太久。如果长时间停留在此状态，可以手动执行一次检查点：CHECKPOINT 。
REPLICATION或DATABASE_MIRRORING	与复制或镜像相关的事务日志记录还未被传递到分发数据库或镜像数据库。	需要检查复制或镜像的配置和运行状态。可能因为网络或性能问题导致同步延迟，需要修复这些组件间的同步。

第三步：通用解决步骤（收缩日志文件）

无论原因为何，当你确认问题已解决（例如，完成了日志备份，或结束了长时间运行的事务）之后，如果日志文件的物理尺寸仍然很大，你可以通过以下步骤将其收缩以释放磁盘空间。
方法一：使用T-SQL命令（推荐，便于操作和自动化）

1. -- 1. 将恢复模式切换为“简单”，以截断日志
2. ALTER DATABASE [数据库名称] SET RECOVERY SIMPLE;
3. GO
5. -- 2. 收缩日志文件。需要先知道日志文件的逻辑名称，可通过下面命令查询：
6. -- SELECT file_id, name FROM sys.database_files;
7. -- 假设日志逻辑文件名为 '数据库名称_Log'
8. DBCC SHRINKFILE (N'数据库名称_Log' , 1, TRUNCATEONLY);
9. GO
11. -- 3. 将恢复模式恢复为“完整”（如果业务需要）
12. ALTER DATABASE [数据库名称] SET RECOVERY FULL;
13. GO

复制代码

特别提醒：切换恢复模式会打断日志链，影响数据库基于时间点的恢复能力。请在操作后立即进行一次完整备份。
方法二：使用SSMS图形化界面

• 右键点击数据库 ERPData > 属性 > 选项，将恢复模式改为简单，然后点击“确定”。
  
• 再次右键点击数据库 >任务>收缩>文件。
  
• 在“文件类型”下拉框中，选择“日志”。
  
• 选择“释放未使用的空间”或指定一个目标大小，点击“确定”。
  
• 收缩完成后，记得按照步骤1的方法，将恢复模式改回“完整”。
  

以上通用方法是针对 log_reuse_wait_desc 值为 LOG_BACKUP 时的常见问题。
而我执行第一步中的sql 查询后，log_reuse_wait_desc 值为 ACTIVE_TRANSACTION。这表明数据库中有一个长时间运行且尚未提交（或回滚）的事务。这个事务阻止了事务日志的截断，导致日志文件持续增长直至占满。
解决log_reuse_wait_desc 值为 ACTIVE_TRANSACTION

要解决这个问题，就需要找到这个活跃事务的具体信息，然后根据情况决定是提交、回滚，还是等待它自然完成。
第一步：查找最旧的活跃事务

运行以下命令来查看数据库 ERPData 中最早的一个活动事务：

1. DBCC OPENTRAN ('ERPData');

复制代码

该命令会返回类似以下的信息：

• 事务的活动时间（例如：2015-03-03 10:30:00）
  
• 发起事务的 SPID（服务器进程ID）
  
• 事务名称（如果有）
  
• 事务的 SID（用户ID）
  

SPID 就是你要找的进程ID，可以进一步查看这个连接正在执行什么语句。
第二步：根据SPID获取详细信息

使用 sp_who2 或 sys.sysprocesses 查看该SPID的详细信息：

1. -- 方法1：使用 sp_who2（结果更易读）
2. EXEC sp_who2 64;   -- 将64替换为实际的SPID
4. -- 方法2：查询 sys.sysprocesses
5. SELECT * FROM sys.sysprocesses WHERE spid = 64;

复制代码

重点关注：

• loginame：哪个登录用户发起的。
  
• hostname：从哪台机器发起的。
  
• lastwaittype：当前等待类型（如果是正在回滚，可能看到ROLLBACK）。
  
• cmd：正在执行的命令（如果是AWAITING COMMAND可能只是空闲连接，但依然持有事务；也可能是SELECT等）。
  
• dbid：确认确实是当前数据库。
  

你还可以使用 DBCC INPUTBUFFER(64) 查看该SPID最近一次执行的SQL语句：

1. DBCC INPUTBUFFER(64);

复制代码

这将大大帮助判断这个事务正在做什么，以及它是否应该被提交或回滚。
执行结果中EventInfo 的值为sql语句，根据此sql语句就能知道具体在干什么了，接下来去优化这个sql或者程序中相关模块的代码即可。
#sql #sqlserver #ssms #数据库 #事务日志已满 #sql2008

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！