sqlserver收缩事务日志失败

梦莱3年前 (2023-02-24)技术文章936

一、背景

2022.01.12 日晚上，sqlserver实例空间使用率达到85%开始告警。查看发现目前日志空间有较大增长，如下图所示：

图片3.png

与客户方进行反馈，在1月13日中午进行事务日志的收缩操作，发现日志空间并无明显变化。

二、过程

1.通过如下命令，先查看一下空间资源情况，可以找到日志空间(log_size)占用较大的数据库。

select DB_NAME(database_id),
SUM(case when type=0 then size else 0 end) * 8/1024 data_size,
sum(case when type=1 then size else 0 end) * 8/1024 log_size,
SUM(case when type > 1 then size else 0 end) * 8/1024 other_size
from sys.master_files
group by DB_NAME(database_id) ;
#单位 MB

2.查看日志空间占用较大的数据库事务日志空间服用等待的情况

select name,log_reuse_wait,log_reuse_wait_desc
from sys.databases;

https://help.aliyun.com/document_detail/147053.htm?spm=a2c4g.11186623.0.0.39224a6aT7EfLw

若为 ACTIVE_TRANSACTION 状态是说明存在长活跃会话事务的，这种情况下无法缩容。需要等待长事物执行完成或者手动 kill 掉该 SQL ，再进行缩容。

3.定位慢 SQL 方式如下。该 SQL 可以定位出执行时间超过十分钟的事务 spid，后续可通过 sys.sysprocesses 表查找出该 SQL 对应的具体事务信息，如 hostname、login_time、program_name等，可以反馈给厂商，看是否可以 kill 掉。

SELECT ST.session_id,ST.transaction_id AS TransactionID ,
        DB_NAME(DT.database_id) AS DatabaseName ,
        AT.transaction_begin_time AS TransactionStartTime ,
        DATEDIFF(minute, AT.transaction_begin_time, GETDATE()) AS Tran_run_time ,
        CASE AT.transaction_type
          WHEN 1 THEN 'Read/Write Transaction'
          WHEN 2 THEN 'Read-Only Transaction'
          WHEN 3 THEN 'System Transaction'
          WHEN 4 THEN 'Distributed Transaction'
        END AS TransactionType ,
        CASE AT.transaction_state
          WHEN 0 THEN 'Transaction Not Initialized'
          WHEN 1 THEN 'Transaction Initialized & Not Started'
          WHEN 2 THEN 'Active Transaction'
          WHEN 3 THEN 'Transaction Ended'
          WHEN 4 THEN 'Distributed Transaction Initiated Commit Process'
          WHEN 5 THEN 'Transaction in Prepared State & Waiting Resolution'
          WHEN 6 THEN 'Transaction Committed'
          WHEN 7 THEN 'Transaction Rolling Back'
          WHEN 8 THEN 'Transaction Rolled Back'
        END AS TransactionState
FROM    sys.dm_tran_session_transactions AS ST
        INNER JOIN sys.dm_tran_active_transactions AS AT ON ST.transaction_id = AT.transaction_id
        INNER JOIN sys.dm_tran_database_transactions AS DT ON ST.transaction_id = DT.transaction_id
WHERE DATEDIFF(minute, AT.transaction_begin_time, GETDATE())>10 -- 找出运行时间大于10分钟的事务
ORDER BY TransactionStartTime