Symptoms
When you use the SSISDB database to store and manage the Microsoft SQL Server Integration Services (SSIS) packages and configure the following properties on SSIS catalog:
-
Clean logs periodically (set to True)
-
Retention period (set to specific number of days –the larger the number of days the more prevalent the problem could be)
-
Periodically remove old versions (set to true)
-
Maximum number of versions per project
You may experience one or more of the following symptoms:
-
It takes a long time to deploy SSIS packages to SSIS database from SSDT.
-
SSIS packages may take a long time to execute or even fail in some cases when the SSISDB cleanup job is running.
-
SQL Server instance may exhibit performance issues.
-
The SSIS maintenance job may take a long time (more than a day) to complete or even fail.
-
The SSISDB may have grown to a large size.
Resolution
Service pack information for SQL Server 2012
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2012. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2755533 How to obtain the latest service pack for SQL Server 2012 After you apply the SQL Server 2012 Service Pack 2 (SP2), follow these steps to fix this issue:
-
Apply SQL Server 2012 SP2 on the SQL Server instance that is hosting SSISB catalog.
-
Run the internal.cleanup_server_log stored procedure in the SSISDB to perform a full cleanup.
EXEC SSISDB.internal.cleanup_server_log
-
Change the SSISDB database to single-user mode.
ALTER DATABASE SSISDB SET SINGLE_USER
-
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' -
Change the SSISDB database back to multi-user mode.
ALTER DATABASE SSISDB SET MULTI_USER
-
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
More Information
SQL Server 2012 Service Pack 2 introduces key design changes that help alleviate this problem:
-
A new property (OPERATION_LOG_ENCRYPTION_LEVEL) is introduced in SSISDB.catalog_property table to enable an option for project-level security. This setting tells SSIS to create one key or certificate pair for each project and reuse it for each transaction, thus minimizing the number of certificates kept and needed to be cleaned up in the future. A full cleanup is required before you change from Transaction-level to project-level.
-
Two stored procedures are introduced to help transit the existing SSISDB from Transaction-level to project-level:
-
cleanup_server_log
-
cleanup_server_execution_keys
For more information about these two procedures and their usage, go to the following website in SQL Server Books Online: -
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
