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)
select name,log_reuse_wait,log_reuse_wait_desc,recovery_model_desc from master.sys.databases
| name | log_reuse_wait | log_reuse_wait_desc | 解释 | 备注 | |
|---|---|---|---|---|---|
| xxx | 0 | NOTHING | 可重用 | - | |
| xxx | 1 | checkpoint | 检查点 | - | |
| xxx | 2 | LOG_BACKUP | 日志备份 | 说明日志待备份 | |
| xxx | 3 | Active backup or restore | 活动备份或还原 | - | |
| xxx | 4 | ACTIVE_TRANSACTION | 活动事务 | 说明有事务没有提交 使用 USE abc; DBCC OPENTRAN 查看最早未提交的事务(可尝试杀掉最早未提交的事务). |
|
| xxx | 5 | Database mirroring | 数据库镜像 | - | |
| xxx | 6 | REPLICATION | 复制 | 若启用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：alwayson环境 - cdc作业停止导致日志无法收缩
案例：2023.10.15.由于alwayson发生了故障转移，之后某db一致无法收缩，执行如下语句看到该db的 log_reuse_wait_desc 为 REPLICATION，经确认该库没有配置replication但是配置了cdc，且发生故障转移之后新的主节点已启用该cdc作业（failover之前为禁用状态）
select name,log_reuse_wait,log_reuse_wait_desc,recovery_model_desc from master.sys.databases
处理过程：
- 第1步：确认 没有长事务、所有辅助节点无延迟、所有辅助节点状态都正常
- 第2步：确认 代理服务已启动（若 Agent 服务未启动则会导致记录数据变化的事务日志处于replication状态，无法重用而将日志空间写满）
- 第3步：发生failover之后，新的主节点上涉及该库的cdc作业（类别为
LogReader，计划类型为代理启动时自动启动，failover之前该job状态为禁用）虽然状态已启用（Agent服务一直为启动状态），但是该作业并未启动执行（注意启用与启动），启动执行该作业却报错如下：
Executed as user: SASACOSMETIC\sqladmin. Another connection is already running 'sp_replcmds' for Change Data Capture in the current database.
[SQLSTATE 42000] (Error 22903) The call to sp_MScdc_capture_job by the Capture Job for database 'xxx' failed.
Look at previous errors for the cause of the failure. [SQLSTATE 42000] (Error 22864).
- 第4步：参考 sp_repldone，在发布服务器上对发布数据库执行如下语句，通过 @reset = 1 设置 all replicated transactions in the log are marked as distributed.（建议将记录cdc变化的表做一下备份）
USE xxx
EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1
- 第5步：检查 cdc的作业发现依旧报错，将执行 sp_repldone 的查询窗口关掉并重新启动该作业，再过一会就可以顺利的备份+收缩日志了。
场景6：存在未完成的备份任务
案例：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