How to fix a failed “SSIS Server Maintenance Job” job

In this article we will explore how to fix a failed “SSIS Server Maintenance Job”.

Introduction

The SSISDB database (a.k.a. the Integration Services catalog) was introduced back in SQL Server 2012 as a way to de-clutter the MSDB database and provide some built-in logging and reporting infrastructure. In a nutshell, SSISDB is an SSIS framework making SQL Server Integration Services more robust and enterprise-friendly by providing the following features:

• Database backup
• Database encryption
• Support for environments
• Support project and package parameters
• Package versioning
• Custom SSRS performance reports built into SSMS
• Deployment directly from SSDT

Since SSISDB Catalog is going to log package run-time information, Microsoft created a SQL Server Agent job (SSIS Server Maintenance Job, see Fig .1) to do the in-house cleaning. This job is running two steps (see Fig 2) to enforce the data retention policy (based on SSISDB Catalog settings) instead of letting all the data collection activities to eat all your disk space and effectively brake your SSIS run-time service (if logging is enabled). 

While SSIS Server Operation Records Maintenance (a.k.a. step #1) is in charge of removing all the old (default=365 days) logging, SSIS Server Max Version Per Project Maintenance (a.k.a. step #2) is in change of removing all old (default=10 versions) package versioning.

Problem

“SSIS Server Maintenance Job” SQL Server Agent job started to fail  with the following error:

Executed as user: #MS_SSISServerCleanupJobLogin##. A cursor with the name ‘execution_cursor’ does not exist. [SQLSTATE 34000] (Error 16916)

This run-time error (trying to deallocate a CURSOR that doesn’t exist) happens inside Step #1 which runs a ssisdb.internal.cleanup_server_retention_window stored procedure. I’m  going to speculate and say that this bug was unintentionally introduced by adding more features (most likely, it’s the server_operation_encryption_level which was introduced with SQL Server 2012 SP2). While I first spotted this problem in SQL Server 2017 with CU12, unfortunately it’s still a problem as of December 2019 with the latest and greatest CU18 (14.0.3257.3).

Solution

You can wait for Microsoft to fix it in a next CU (a.k.a. Cumulative Update) or you can fix it yourself by modifying the code inside ssisdb.internal.cleanup_server_retention_window stored procedure.

A little warning here: You are going to be modifying the code that was created by Microsoft. You need to be comfortable with taking that risk.

You would need to alter the procedure by moving a DEALLOCATE statement from line #175 to #177 to fix this failed SSIS job (see Fig #3).

This post is based on this MSDN post.