sqlserver日志无法收缩

导致 SQL Server 日志收缩不掉的原因有很多种，下面讲述几个常见的场景。

---

[TOC]

知识点：几个常用的dbcc命令

dbcc sqlperf

• 所有DB的LOG大小，找出日志最大的库。

DBCC SQLPERF(LOGSPACE)
  --Database Name   Log Size (MB)   Log Space Used (%)  Status
  --WSS_Content     517621.4        99.98032            0

dbcc opentran

• 查看当前DB已打开的、最早的、活动事务。
• 识别可能阻止日志截断的活动事务，如果没有活动事务，就显示信息性消息。
• 括号内加上数据库名称，如果选定为当前库可以为空。

USE xxx; DBCC OPENTRAN
USE xxx; DBCC OPENTRAN WITH TABLERESULTS

--.如下2行效果等于如上2行效果
DBCC OPENTRAN('xxx')
DBCC OPENTRAN('xxx') WITH TABLERESULTS

dbcc loginfo

• 查询事务日志的状态。
• 注意：只有status=0(free状态)的虚拟日志文件才会被收缩

USE xxx; DBCC LOGINFO

sys.databases

• 其中 log_reuse_wait 字段，日志空间的重复使用正在等待最后一个检查点的描述
• 可参考：sys.databases (Transact-SQL)

use master;
select name,log_reuse_wait,log_reuse_wait_desc from sys.databases

name	log_reuse_wait	log_reuse_wait_desc	解释	备注
xxx	0	NOTHING	无
xxx	1	checkpoint	检查点
BF_POS	2	LOG_BACKUP	日志备份	说明日志待备份
xxx	3	Active backup or restore	活动备份或还原
WSS_Content	4	ACTIVE_TRANSACTION	活动事务	说明有事务没有提交 使用 USE abc; DBCC OPENTRAN 查看最早未提交的事务(可尝试杀掉最早未提交的事务).
xxx	5	Database mirroring	数据库镜像
xxx	6	Replication	复制	2021.03.24.启用cdc若agent服务停止也会造成Replication状态
xxx	7	Database snapshot creation	数据库快照创建
xxx	8	Log scan	日志扫描
xxx	9	AVAILABILITY_REPLICA	辅助副本正将事务日志应用到数据库	若配置了ag请确认所有节点和db状态都正常

场景1：设为简单模式再收缩日志

限制：此方案仅限单实例场景，即：没有配置mirror或alwayson的场景；

• 注意1：如果配置了mirror或alwayson则不要尝试此方案。因为一旦设置为简单模式，就会把mirror和alwayson断开，需要重新重新搭建；
• 注意2：设置简单模式会导致日志链不连续，无法通过日志来恢复精准数据，请慎重；

具体过程：

• 1.先切换上下文，再设置该数据库（以xxx为例）为单用户模式

  解读：设置简单模式之前，先设置单用户模式的原因是为了断开其他用户的连接（请慎重），否则直接设置简单模式可能会超时。

USE xxx
ALTER DATABASE xxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE

• 2.设置为简单模式

ALTER DATABASE xxx SET RECOVERY SIMPLE WITH NO_WAIT

• 3.收缩当前数据库日志文件大小为0(只读/紧急模式无法收缩)

DBCC SHRINKFILE(2,0,TRUNCATEONLY)

• 4.最后再改回多用户模式

ALTER DATABASE xxx SET MULTI_USER

场景2：由于事务未提交而导致日志无法收缩

• 1.使用 exec sp_who2 查看进程

  比如：看到大量进程被spid=31阻塞，而spid=31的进程占用大量CPUTime及DiskIO，状态为BACKGROUND，Command为DB STARTUP

• 2.使用dbcc loginfo看到大量 status=2 的记录

use xxx
dbcc loginfo

• 3.查看是否有未提交的事务：

SELECT 
   'kill ' + CAST(a.session_id AS NVARCHAR(100))
  ,a.session_id,connect_time,host_name,c.[text]program_name,host_process_id,client_version 
  ,client_interface_name,a.login_name,a.status,cpu_time,total_elapsed_time,total_scheduled_time 
  ,last_request_start_time,reads,writes,logical_reads,original_login_name,client_net_address,client_tcp_port,most_recent_sql_handle 
FROM sys.dm_exec_sessions a inner join SYS.DM_EXEC_CONNECTIONS b ON a.session_id = b.session_id CROSS APPLY SYS.DM_EXEC_SQL_TEXT(most_recent_sql_handle) c 
WHERE a.session_id <> @@SPID 
ORDER BY connect_time

• 4.杀掉一直未提交的最早的几个事务

KILL ***

• 5.再次查看可看到很多status原来是2的已经变为0.

use xxx
dbcc loginfo

场景3：mirror环境 - 日志无法截断

由于不能设为简单模式再收缩日志，所以只能通过备份日志+收缩日志的方式。

• 1.备份该数据库（以xxx为例）的日志

USE master
BACKUP LOG xxx TO DISK = 'D:\sqlbak\xxx.20201225.trn' WITH COMPTESSION,STATS=1

• 2.收缩当前数据库日志文件大小为0(只读/紧急模式无法收缩)

USE xxx
DBCC SHRINKFILE(2,0,TRUNCATEONLY)

提醒：backup log一遍没有效果的话，多执行几遍backup log再收缩日志文件（真的管用）。

场景4：alwayson环境 - 节点故障导致日志无法收缩

延迟过高导致

案例：2019.04.15.即使多次备份日志也一直无法释放(log_reuse_wait=2 即 日志待备份)，后找到ag中延迟最大的节点，清除缓冲池(buffer pool)等没有延迟就恢复正常了。

--.清除缓冲池(buffer pool)中所有缓存的数据页面
USE master
DBCC DROPCLEANBUFFERS

辅助节点宕机导致

案例：2020.02.14.即使多次备份日志也一直无法释放(log_reuse_wait=9 即 AVAILABILITY_REPLICA)，ag中其中一个节点sql03宕机（没有监控一直未被发现，查看ag延迟才发现），导致主库日志文件一直无法收缩，后来将sql03开机则恢复正常。

--.查看ag延迟
SELECT  ar.replica_server_name AS [副本名称] ,
        DB_NAME(dbr.database_id) AS [数据库名称] ,
        ISNULL(CASE dbr.redo_rate WHEN 0 THEN -1
               ELSE CAST(dbr.redo_queue_size AS FLOAT) / dbr.redo_rate
               END, -1) AS [Redo延迟(秒)] ,
        ISNULL(CASE dbr.log_send_rate WHEN 0 THEN -1
               ELSE CAST(dbr.log_send_queue_size AS FLOAT) / dbr.log_send_rate
               END, -1) AS [Log传送延迟(秒)] ,
        dbr.redo_queue_size AS [Redo等待队列(KB)] ,
        dbr.redo_rate AS [Redo速率(KB/S)] ,
        dbr.log_send_queue_size AS [Log传送等待队列(KB)] ,
        dbr.log_send_rate AS [Log传送速率(KB\S)]
FROM    [master].sys.availability_replicas AS AR
INNER JOIN [master].sys.dm_hadr_database_replica_states AS dbr ON ar.replica_id = dbr.replica_id
WHERE   dbr.redo_queue_size IS NOT NULL

辅助节点db断开或置疑导致

案例：2020.09.14.即使多次备份日志也一直无法释放(log_reuse_wait=9 即 AVAILABILITY_REPLICA)，后来发现辅助节点该db状态为“未同步/置疑”，修复以后则恢复正常。

场景5：存在未完成的备份任务

案例：2022.12.03.由于日志文件过大导致d盘空间爆掉，对最大日志执行备份，收缩日志的时候报错“对数据库的备份、文件操作(如ALL DATABASE ADD FILE)以及加密更改必须序列化。请在当前备份或文件操作完成后重新发出该语句”

• 解决：执行如下语句看到有大量3天前的 backup database 进程，应该是数据库太大导致当天的备份任务未完成而出现排队，手动kill并重试就ok了

--.查看正在备份/还原的进度百分比. 
SELECT 
   DB_NAME(database_id) AS Exec_DB 
  ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2 
  ,(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
         ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) 
  ,percent_complete 
FROM SYS.DM_EXEC_REQUESTS qs (nolock) 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
WHERE command LIKE 'BACKUP%' OR command LIKE 'RESTORE%' 
ORDER BY 2 DESC