catalog.cleanup_server_log

  • Article

Cleans up operation logs to bring the SSISDB database into a state that lets you change the value of the SERVER_OPERATION_ENCRYPTION_LEVEL property.

Syntax

cleanup_server_log

Arguments

None.

Remarks

The service pack 2 (SP2) for SQL Server 2012 adds the 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. However, this setting 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.

To change the SERVER_OPERATION_ENCRYPTION_LEVEL from 1 to 2 (or) from 2 to 1, 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 this stored procedure (cleanup_server_log).

  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.

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

Example

The following example invokes the cleanup_server_log stored procedure.

USE [SSISDB]
GO

DECLARE@return_value int
EXEC@return_value = [internal].[cleanup_server_log]
SELECT'Return Value' = @return_value
GO

Return Code Value

0 for success and 1 for failure

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

Errors and Warnings

The stored procedure raises errors in the following scenarios:

  • There are one or more active operations in the SSISDB database.

  • The SSISDB database is not in single user mode.