Question

如何有效控制SSISDB的数据库文件的大小

Answer 1

1. 设置SSIDB Properties，仅保留1天的日志和3个project部署版本
SSISDB会创建一个agent job - SSIS Server Maintenance Job，根据以上配置来定期清理SSISDB内容。这个job的第一步会调用[SSISDB].[internal].[cleanup_server_retention_window]存储过程，涉及多表的级联删除，会产生大量transaction log。所以我们还需要进行以下操作。
2. SSISDB数据库的恢复模式默认是Full，如果不进行日志备份并截断，SSISDB.ldf会持续增长，很快占满磁盘。考虑到SSIDB并没有容灾要求，直接将恢复模式改为Simple Recovery，这样的好处是数据库Checkpoint之后会自动截断transaction log，SSISDB.ldf会控制在一个稳定的大小。但稳定值在40G左右还是无法接受，需要进一步优化
3. 在SSISDB中查看[internal].[cleanup_server_retention_window]的代码，在删除日志的核心代码中加入CHECKPOINT，促使数据库在每个级联删除事务之后尽快进行checkpoint，从而自动截断transaction log，下一轮循环中的删除操作就可以重用该文件空间；同时，减少@delete_batch_size为100，使每个批次中删除数据量减少。最终SSISDB.ldf稳定大小缩小至20G左右。
注意：需要给AllSchemaOwner赋予CHECKPOINT权限
4. 设置Customized Logging Level，只记录error和warning信息，这样就大大减少了写入SSIDB表中的记录数。DELETE时的事务日志自然也缩减了