#!/usr/bin/env bash
# 盈尺（Surinch）SQL Server 事务日志暴涨 / 收缩不动 复现脚本 v1.0（2026-11-02，在 SQL Server 2022 CU27-GDR 16.0.4305.5 Developer 容器上跑通）
# 复现：E1 FULL 但没完整备份 = 伪简单模式；E2 不做日志备份直接 SHRINKFILE 缩不动；
#       E3 一个 1 行没提交的事务让日志备份也释放不了空间，MAXSIZE 下报 9002，reuse_wait 却显示 LOG_BACKUP；
#       E4 “切 SIMPLE + 收缩 + 切回 FULL” 后 BACKUP LOG 报 4214，日志链断开；E5（可选）不同自动增长设置的耗时和 VLF 数。
# !!! 只能在测试实例上跑：会 DROP/CREATE 数据库 loglab、vlflab，改恢复模式，在容器数据目录写 .bak/.trn。
# 用法：
#   SQLCMD="docker exec -i mssql-lab /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P '<测试密码>' -C" bash sqlserver-log-full-shrink-demo-v1.0.sh | tee demo.txt
#   想同时跑 E5（约 5 分钟）：RUN_E5=1 SQLCMD=... bash sqlserver-log-full-shrink-demo-v1.0.sh
#   BACKUP_DIR 默认 /var/opt/mssql/data（容器内路径）
# 状态行列：model|reuse_wait|log_mb|used_mb|used_pct|vlf|vlf_active|last_log_backup_lsn
# 相关文章：https://surinch.com/resources/sql-server-transaction-log-full-shrink-log-reuse-wait
set -uo pipefail
: "${SQLCMD:?请设置 SQLCMD，例如 docker exec -i mssql-lab /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P xxx -C}"
BK=${BACKUP_DIR:-/var/opt/mssql/data}
sql(){ local db=$1; shift; eval "$SQLCMD -I -W -s'|' -h -1 -d $db" 2>&1 | grep -vE '^\s*$|rows affected'; }
st(){ printf '%-28s | ' "$1"; sql loglab <<'TSQL' | tr '\n' ' '; echo; }
SET NOCOUNT ON;
SELECT CONCAT(d.recovery_model_desc,'|',d.log_reuse_wait_desc,'|',
 CAST(u.total_log_size_in_bytes/1048576.0 AS decimal(10,1)),'|',CAST(u.used_log_space_in_bytes/1048576.0 AS decimal(10,1)),'|',
 CAST(u.used_log_space_in_percent AS decimal(5,1)),'|',(SELECT COUNT(*) FROM sys.dm_db_log_info(DB_ID())),'|',
 (SELECT COUNT(*) FROM sys.dm_db_log_info(DB_ID()) WHERE vlf_active=1),'|',ISNULL(CAST(rs.last_log_backup_lsn AS varchar(30)),'NULL'))
FROM sys.databases d CROSS JOIN sys.dm_db_log_space_usage u JOIN sys.database_recovery_status rs ON rs.database_id=d.database_id WHERE d.name='loglab';
TSQL
work(){ sql loglab <<'TSQL' | tr '\n' ' '; }
SET NOCOUNT ON;
DECLARE @t datetime2=SYSDATETIME();
UPDATE dbo.orders SET amount=amount+0.01, note=REPLICATE(CHAR(65+DATEPART(second,SYSDATETIME())%26),400), updated_at=SYSDATETIME();
SELECT CONCAT('work_ms=',DATEDIFF(millisecond,@t,SYSDATETIME()));
CHECKPOINT;
TSQL
logbk(){ echo "SET NOCOUNT ON; DECLARE @t datetime2=SYSDATETIME(); BACKUP LOG loglab TO DISK='$BK/loglab_$1.trn' WITH INIT, FORMAT, COMPRESSION; SELECT CONCAT('log_backup_ms=',DATEDIFF(millisecond,@t,SYSDATETIME()));" | sql master | grep -E 'log_backup_ms|Msg|cannot' | tr '\n' ' '; echo; }
shrink(){ echo "SET NOCOUNT ON; DECLARE @t datetime2=SYSDATETIME(); DBCC SHRINKFILE (loglab_log, 64) WITH NO_INFOMSGS; SELECT CONCAT('shrink_ms=',DATEDIFF(millisecond,@t,SYSDATETIME()));" | sql loglab | grep -E 'shrink_ms|Msg' | tr '\n' ' '; echo; }

echo "##### version"; echo "SELECT @@VERSION;" | sql master | head -1

echo "##### E1 建库：数据 1GB，日志 8MB/每次增长 64MB，FULL；50 万行自造数据"
sql master <<'TSQL'
SET NOCOUNT ON;
IF DB_ID('loglab') IS NOT NULL BEGIN ALTER DATABASE loglab SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE loglab; END
CREATE DATABASE loglab ON (NAME=loglab_data, FILENAME='/var/opt/mssql/data/loglab.mdf', SIZE=1024MB, FILEGROWTH=256MB)
 LOG ON (NAME=loglab_log, FILENAME='/var/opt/mssql/data/loglab_log.ldf', SIZE=8MB, FILEGROWTH=64MB);
GO
ALTER DATABASE loglab SET RECOVERY FULL;
GO
USE loglab;
CREATE TABLE dbo.orders(id int IDENTITY PRIMARY KEY, shop_id int NOT NULL, status tinyint NOT NULL, amount decimal(12,2) NOT NULL, note char(400) NOT NULL, updated_at datetime2 NOT NULL);
CREATE TABLE dbo.job_log(id int IDENTITY PRIMARY KEY, msg varchar(100), at datetime2 DEFAULT SYSDATETIME());
GO
;WITH n AS (SELECT TOP (500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i FROM sys.all_objects a CROSS JOIN sys.all_objects b CROSS JOIN sys.all_objects c)
INSERT dbo.orders(shop_id,status,amount,note,updated_at) SELECT i%1000, i%5, (i%9973)/10.0, REPLICATE('x',400), DATEADD(second,i,'2026-01-01') FROM n;
CHECKPOINT;
TSQL
st "建库后"
for i in 1 2 3; do work; st "无完整备份 第${i}轮"; done
echo "SET NOCOUNT ON; DECLARE @t datetime2=SYSDATETIME(); BACKUP DATABASE loglab TO DISK='$BK/loglab_full.bak' WITH INIT, FORMAT, COMPRESSION; SELECT CONCAT('full_backup_ms=',DATEDIFF(millisecond,@t,SYSDATETIME()));" | sql master | grep full_backup_ms
st "完整备份后"
for i in 1 2 3; do work; st "有完整备份无日志备份 第${i}轮"; done

echo "##### E2 收缩：先直接缩，再日志备份后缩"
shrink; st "直接收缩到64MB后"
logbk e2_1; st "日志备份1后"
shrink; st "再收缩后"
logbk e2_2; st "日志备份2后"
shrink; st "再收缩后"

echo "##### E3 一个 1 行没提交的事务 + MAXSIZE=640MB"
echo "ALTER DATABASE loglab MODIFY FILE (NAME=loglab_log, MAXSIZE=640MB);" | sql master
( printf "SET NOCOUNT ON; BEGIN TRAN; INSERT dbo.job_log(msg) VALUES('nightly job start'); WAITFOR DELAY '00:01:30'; COMMIT; SELECT 'opentx committed';" | sql loglab ) &
OPEN_PID=$!
sleep 3
work; st "开着事务 更新1轮后"
logbk e3_1; st "日志备份后"
echo "-- 只读：谁拖住了日志"
sql loglab <<'TSQL'
SET NOCOUNT ON;
SELECT TOP (3) CONCAT('session=',s.session_id,' login=',s.login_name,' program=',s.program_name,' status=',s.status,' cmd=',r.command,' tx_age_s=',DATEDIFF(second,dt.database_transaction_begin_time,SYSDATETIME()))
FROM sys.dm_tran_database_transactions dt JOIN sys.dm_tran_session_transactions st ON st.transaction_id=dt.transaction_id
JOIN sys.dm_exec_sessions s ON s.session_id=st.session_id LEFT JOIN sys.dm_exec_requests r ON r.session_id=s.session_id
WHERE dt.database_id=DB_ID() ORDER BY dt.database_transaction_begin_time;
DBCC OPENTRAN WITH NO_INFOMSGS;
TSQL
echo "-- 第 2 轮更新（预计撞 MAXSIZE 报 9002）"; work; echo; st "撞上限后"
logbk e3_2; st "开着事务 再日志备份后"
wait $OPEN_PID
st "事务提交后(未备份)"
logbk e3_3; st "提交后日志备份"
echo "ALTER DATABASE loglab MODIFY FILE (NAME=loglab_log, MAXSIZE=UNLIMITED);" | sql master

echo "##### E4 切 SIMPLE + CHECKPOINT + 收缩 + 切回 FULL"
work; st "更新后"
echo "ALTER DATABASE loglab SET RECOVERY SIMPLE;" | sql master
echo "CHECKPOINT; DBCC SHRINKFILE (loglab_log, 64) WITH NO_INFOMSGS;" | sql loglab
echo "ALTER DATABASE loglab SET RECOVERY FULL;" | sql master
st "切回 FULL 后"
logbk e4_1
work; st "切回后再更新1轮"
echo "SET NOCOUNT ON; DECLARE @t datetime2=SYSDATETIME(); BACKUP DATABASE loglab TO DISK='$BK/loglab_e4_diff.bak' WITH DIFFERENTIAL, INIT, FORMAT, COMPRESSION; SELECT CONCAT('diff_ms=',DATEDIFF(millisecond,@t,SYSDATETIME()));" | sql master | grep diff_ms
logbk e4_2; st "差异+日志备份后"
echo "SET NOCOUNT ON; SELECT TOP 6 CONCAT(type,' ',CONVERT(varchar(19),backup_start_date,120),' first=',first_lsn,' last=',last_lsn) FROM msdb.dbo.backupset WHERE database_name='loglab' ORDER BY backup_set_id DESC;" | sql master

if [ "${RUN_E5:-0}" = 1 ]; then
echo "##### E5 同一个约 900MB 日志的大事务，不同自动增长设置"
for cfg in "8MB 1MB" "8MB 10%" "8MB 64MB" "2048MB 512MB"; do
 set -- $cfg
 sql master <<TSQL >/dev/null
SET NOCOUNT ON;
IF DB_ID('vlflab') IS NOT NULL BEGIN ALTER DATABASE vlflab SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE vlflab; END
CREATE DATABASE vlflab ON (NAME=vlflab_data, FILENAME='/var/opt/mssql/data/vlflab.mdf', SIZE=1024MB, FILEGROWTH=256MB)
 LOG ON (NAME=vlflab_log, FILENAME='/var/opt/mssql/data/vlflab_log.ldf', SIZE=$1, FILEGROWTH=$2);
GO
ALTER DATABASE vlflab SET RECOVERY FULL;
GO
USE vlflab;
CREATE TABLE dbo.orders(id int IDENTITY PRIMARY KEY, shop_id int NOT NULL, status tinyint NOT NULL, amount decimal(12,2) NOT NULL, note char(400) NOT NULL, updated_at datetime2 NOT NULL);
;WITH n AS (SELECT TOP (500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i FROM sys.all_objects a CROSS JOIN sys.all_objects b CROSS JOIN sys.all_objects c)
INSERT dbo.orders(shop_id,status,amount,note,updated_at) SELECT i%1000, i%5, (i%9973)/10.0, REPLICATE('x',400), DATEADD(second,i,'2026-01-01') FROM n;
CHECKPOINT;
GO
BACKUP DATABASE vlflab TO DISK='$BK/vlflab_full.bak' WITH INIT, FORMAT, COMPRESSION;
TSQL
 printf '初始=%-6s 增长=%-5s | ' "$1" "$2"
 sql vlflab <<'TSQL'
SET NOCOUNT ON;
DECLARE @t datetime2=SYSDATETIME();
BEGIN TRAN;
UPDATE dbo.orders SET amount=amount+0.01, note=REPLICATE('A',400), updated_at=SYSDATETIME();
UPDATE dbo.orders SET amount=amount+0.01, note=REPLICATE('B',400), updated_at=SYSDATETIME();
COMMIT;
SELECT CONCAT('work_ms=',DATEDIFF(millisecond,@t,SYSDATETIME()),' log_mb=',CAST(u.total_log_size_in_bytes/1048576.0 AS decimal(10,1)),' vlf=',(SELECT COUNT(*) FROM sys.dm_db_log_info(DB_ID())))
FROM sys.dm_db_log_space_usage u;
TSQL
done
fi
echo "##### done（测试库 loglab/vlflab 保留，自行 DROP）"