catalog.cleanup_server_execution_keys

  • Article

Drops certificates and symmetric keys from the SSISDB database.

Syntax

clean_server_execution_keys [ @cleanup_flag = ] cleanup_flag
[ @delete_batch_size = ] delete_batch_size

Arguments

  • [ @cleanup\_flag = ] cleanup_flag
    Indicates whether execution level (1) or project level (2) certificates/symmetric keys to be dropped.

    Use execution level (1) only when the SERVER_OPERATION_ENCRYPTION_LEVEL is set to PER_PROJECT (2).

    Use project level (2) only when the SERVER_OPERATION_ENCRYPTION_LEVEL is set to PER_EXECUTION (1) or SERVER_OPERARATION_ENCRYPTION_LEVEL is set to (2) but the projects have been deleted and all the operation logs for the projects have been cleaned.

  • [ @delete\_batch\_size = ] delete_batch_size
    Number of keys/certificates to be dropped. The default value is 1000.

Remarks

The service pack 2 for SQL Server 2012 adds a new property named SERVER_OPERATION_ENCRYPTION_LEVEL property to the internal.catalog_properties table. This property has two possible values:

  1. PER_EXECUTION (1) – The certificate and symmetric key used for protecting sensitive execution parameters and execution logs are created for each execution. This is the default value. . You may run into performance issues (deadlocks, failed maintenance jobs etc…) in a production environment because certificate/keys are generated for each execution. This provides a higher level of security than the other value (2).

  2. PER_PROJECT (2) – The certificate and symmetric key used for protecting sensitive parameters are created for each project. This gives you a better performance than the PER_EXECUTION level because the key and certificate are generated once for a project rather than for each execution.

You must execute the catalog.cleanup_server_log stored procedure before changing the SERVER_OPERATION_ENCRYPTION_LEVEL from 1 to 2 (or) from 2 to 1. Before executing this stored procedure, do the following:

  1. Ensure that the value of the property OPERATION_CLEANUP_ENABLED is set to TRUE in the catalog.catalog_properties (SSISDB Database) table.

  2. Set the Integration Services database (SSISDB) to be in single-user mode. In SQL Server Management Studio, launch Database Properties dialog box for SSISDB, switch to the Options tab, and set the Restrict Access property to single-user mode (SINGLE_USER). After you executed the cleanup_server_log stored procedure, set the property value back to the original value.

  3. Execute the catalog.cleanup_server_log stored procedure.

  4. Now, go ahead and change the value for the SERVER_OPERATION_ENCRYPTION_LEVEL property in the catalog.catalog_properties (SSISDB Database) table.

  5. Execute the catalog.cleanup_server_execution_keys stored procedure to clean up certificates keys from the SSISDB database. Dropping certificates/keys from the SSISDB database may take a long time, so it should be run periodically during off-peak times.

    You can specify the scope or level (execution vs. project) and number of keys to be deleted. The default batch size for deletion is 1000. When you set the level to 2, the keys and certificates are deleted only if the associated projects have been deleted.

See the following Knowledge Base article for additional details: 2972285.

Example

The following example calls cleanup_server_execution_keys stored procedure.

USE [SSISDB]
GO

DECLARE@return_value int

EXEC@return_value = [internal].[cleanup_server_execution_keys]
@cleanup_flag = 1,
@delete_batch_size = 500

SELECT'Return Value' = @return_value

GO

Return Code Value

0 for success

Result Sets

None

Permissions

This stored procedure requires one of the following permissions:

  • READ and EXECUTE permissions on the project and, if applicable, READ permissions on the referenced environment

  • Membership to the ssis_admin database role

  • Membership to the sysadmin server role