MSSQL故障处理之事务日志增长过快

MSSQL故障处理之事务日志增长过快

问题背景：
客户反馈sqlserver数据库日志增长迅速，两分钟增长好几个g，目前数据库已暂时改为简单模式

sqlserver 日志文件不停增长的原因
日志不停增长的原因

1.数据库是完整模式，但是并没有定期的进行日志备份。日志备份可以截断事务，可以使得空间重用。
解决这个问题，只需做好日志定时备份的计划作业就行

2.有事务长时间没有提交
由于开发人员的粗心大意，没有把已经运行完成的事务提交，日志一直在记录，导致很大
解决这个问题，查找出已经运行完成但没有提交的事务，kill掉此事务即可

3.有很大的事务正在运行
这个事务很大，一直不停的在记录大量的日志，导致日志增大
解决这个问题，看看在语句和业务逻辑上看看能否优化的余地，运行很大的事务能否分事务运行

造成2,3两种情况的根本原因是因为：日志备份只备份已提交的事务
还需要注意的是：只有日志备份才能截断日志，使得日志空间可以重用！！！

问题排查：

DBCC SQLPERF(LOGSPACE)
GO
SELECT name,recovery_model_desc,log_reuse_wait,log_reuse_wait_desc
FROM sys.databases
GO

如果大部分日志都在使用中且重用状态为：ACTIVE_TRANSACTION，那么要看看最久的事务是谁申请的

步骤2：检查最久的活动事务：
如果大部分日志都在使用中且重用状态为：ACTIVE_TRANSACTION，那么要看看最久的事务是谁申请的：

DBCC OPENTRAN
GO
SELECT *
FROM sys.dm_exec_sessions AS t2 ,
sys.dm_exec_connections AS t1
CROSS APPLY sys.dm_exec_sql_text(t1.most_recent_sql_handle) AS st
WHERE t1.session_id = t2.session_id
AND t1.session_id > 50

查看spid为314的进程，text为一条删除语句，

delete from tablename

如果Log Space Used(%)很高，就要马上定位为什么不能被清除。

如果状态为：LOG_BACKUP，意味着SQLServer等待着日志备份。要检查是否需要做日志备份。

此现象符合
2.有事务长时间没有提交
由于开发人员的粗心大意，没有把已经运行完成的事务提交，日志一直在记录，导致很大
解决这个问题，查找出已经运行完成但没有提交的事务，kill掉此事务即可

3.有很大的事务正在运行
这个事务很大，一直不停的在记录大量的日志，导致日志增大
解决这个问题，看看在语句和业务逻辑上看看能否优化的余地，运行很大的事务能否分事务运行

需要程序层面及时提交事务