SSISDB 数据清理

SSIS Catalog Database

可以管理很多的包，并且查询每个包的执行状态都很方便。但是如果其中包含的包太多，而且包的执行频次很高，

所产生的日志就是一个灾难了。SSIS目录服务打开之后靠自带的作业“SSIS Server Maintenance Job”来清理包的执行

日志，清理的标准呢？

呃，SSIS包的日志默认会保留365天，你没看错，项目版本是保留7个版本。日志的基本根据个人喜好可以设置，但是保留的天数还是设置短点。

一般根据包的总量和执行频次综合设置一个保留天数就好。日志保留时间越长，ssisdb空间就越大，而且部署包和查询包日志所需要的时间就越多。

如果一个机器开始是新部署的ssis目录服务，把上述指标设置好，在保证作业定时执行，一般是不出问题的。

-------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------

实际情况往往很糟糕，一般都是发现问题，如ssis包日志查询不出来，部署需要好长时间才去找原因，在改上图的参数，去执行清理作业会发现根本就删除不动

于是有了下文：

官方地址：http://support.microsoft.com/kb/2972285/en-us

下面列出关键点：

1、Apply SQL Server 2012 SP2 on the SQL Server instance that is hosting SSISB catalog.

需要给sql server 2012 打上补丁

2、Change the SSISDB database to single-user mode.

ALTER DATABASE SSISDB SET SINGLE_USER

3、Run the internal.cleanup_server_log stored procedure in the SSISDB to perform a full cleanup.

EXEC SSISDB.internal.cleanup_server_log

第三步也不会很顺利，执行发现得到了下面的错误。运气好，把Intergration Service 服务停了，就不会出现这个问题了。

运气不好的时候，怎么停服务，怎么看正在运行的包里面都没有任何东西，还是不能清理日志。打开这个存储过程内容看一下

IF EXISTS (SELECT operation_id FROM [internal].[operations]
        WHERE [status] IN (2, 5)
        AND   [operation_id] <> @operation_id )
BEGIN   
    RAISERROR(27139, 16, 1) WITH NOWAIT
    RETURN 1
END

这段代码决定了返回的错误，原来执行这个存储过程会去“operations”表查找status是否包含2，5的记录，如果有，那么就返回不让执行

把这个表包含2，5的记录更新成其他的，在执行即可。（出现这个问题的原因不清楚，有可能是数据不同步等原因造成的，谁知道呢我们的任务是清理日志）

4、Call configure_catalog together with SEVER_OPERATION_ENCRYPTION_LEVEL parameter to change the operation log’s encryption level to

PER_PROJECT (2) from the default of PER_EXECUTION (1)

EXEC SSISDB.catalog.configure_catalog @property_name=‘SERVER_OPERATION_ENCRYPTION_LEVEL‘,
@property_value=‘2‘

5、Change the SSISDB database back to multi-user mode

ALTER DATABASE SSISDB SET MULTI_USER

6、Run the internal.Cleanup_Server_execution_keys stored procedure to clean up transaction level keys.

EXEC SSISDB.internal.Cleanup_Server_execution_keys @cleanup_flag = 1

ok，后面的步骤基本上没有什么问题，设置一些参数而已。都弄完了，可以感受一下非一般的速度了。