求助：SQL Server 2016中SSISDB无法清理（已达70GB）

我完全懂这种SSISDB膨胀但清理脚本死活不起作用的挫败感——70GB的体量不仅占空间，还可能拖慢整个SSIS服务的响应。咱们一步步来排查，把这个问题解决掉。

一、先确认清理配置是否真的生效 #

首先得确保你的保留期和自动清理设置没有配置错误：

• 执行下面的查询，核对当前的SSISDB配置：

SELECT property_name, property_value 
FROM catalog.catalog_properties 
WHERE property_name IN ('RETENTION_WINDOW', 'CLEANUP_LOGS_PERIODICALLY');

• 注意RETENTION_WINDOW的单位是天，如果设置得过大（比如365天），那清理脚本自然不会删除多少数据。另外一定要确认CLEANUP_LOGS_PERIODICALLY的值确实是True。

二、排查存储过程执行的问题 #

你提到执行了cleanup_server_retention_window和cleanup_server_log没效果，那咱们得搞清楚这些过程是执行失败了，还是被阻塞了：

• 执行存储过程时加上返回值检查，看看是否执行成功：

DECLARE @cleanup_result INT;
EXEC @cleanup_result = catalog.cleanup_server_retention_window;
SELECT '清理执行结果' = @cleanup_result;

返回值0表示成功，1表示失败。如果失败，去查看SQL Server的错误日志，或者查询SSISDB的操作消息表找线索：

SELECT * FROM catalog.operation_messages 
WHERE message_time >= DATEADD(HOUR, -2, GETDATE()) 
ORDER BY message_time DESC;

• 另外，清理过程可能被长运行的SSIS包或者数据库锁阻塞。用下面的查询查看当前的锁和阻塞情况：

SELECT 
    request_session_id AS 会话ID,
    resource_type AS 资源类型,
    resource_description AS 资源描述,
    request_mode AS 锁模式,
    request_status AS 请求状态
FROM sys.dm_tran_locks 
WHERE resource_database_id = DB_ID('SSISDB');

如果发现有阻塞，先确认对应的会话是安全的，再终止它。

三、手动清理特定操作记录（自动清理失效时的备选方案） #

如果自动清理还是不工作，可以尝试手动批量删除旧的操作记录：

1. 先找出超过保留期的操作ID：

DECLARE @retention_days INT;
SELECT @retention_days = CAST(property_value AS INT) 
FROM catalog.catalog_properties 
WHERE property_name = 'RETENTION_WINDOW';

SELECT operation_id 
FROM catalog.operations 
WHERE end_time < DATEADD(DAY, -@retention_days, GETDATE())
ORDER BY operation_id DESC;

2. 用游标批量调用单个操作的清理存储过程（避免一次性删太多导致锁表）：

DECLARE @op_id INT;
DECLARE op_cursor CURSOR FOR 
SELECT operation_id 
FROM catalog.operations 
WHERE end_time < DATEADD(DAY, -@retention_days, GETDATE());

OPEN op_cursor;
FETCH NEXT FROM op_cursor INTO @op_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC catalog.cleanup_operation @operation_id = @op_id;
    FETCH NEXT FROM op_cursor INTO @op_id;
END

CLOSE op_cursor;
DEALLOCATE op_cursor;

3. 清理完成后，如果需要，可以收缩数据库文件（注意：不要频繁收缩，只在必要时操作）：

USE SSISDB;
DBCC SHRINKFILE (SSISDB_Data, 10000); -- 这里设置目标大小，比如10GB
DBCC SHRINKFILE (SSISDB_Log, 2000); -- 日志文件目标大小，比如2GB

四、优化SSISDB的索引和统计信息 #

长期运行后，SSISDB的索引可能碎片化严重，导致清理过程执行缓慢甚至超时：

• 查看索引碎片化情况：

SELECT 
    OBJECT_NAME(ips.object_id) AS 表名,
    i.name AS 索引名,
    ips.avg_fragmentation_in_percent AS 碎片化百分比
FROM sys.dm_db_index_physical_stats(DB_ID('SSISDB'), NULL, NULL, NULL, 'DETAILED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30
ORDER BY ips.avg_fragmentation_in_percent DESC;

• 重建碎片化严重的索引：

ALTER INDEX ALL ON catalog.operations REBUILD;
ALTER INDEX ALL ON catalog.operation_messages REBUILD;

• 更新统计信息，帮助查询优化器生成更高效的执行计划：

USE SSISDB;
UPDATE STATISTICS catalog.operations;
UPDATE STATISTICS catalog.operation_messages;

内容的提问来源于stack exchange，提问作者rick van der horst