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

failed SSIS

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). 

failed SSIS
Figure 1: The Job

 

Figure 2: Job Steps

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).

Figure 3: Solution

This post is based on this MSDN post.

Please share this

This Post Has One Comment

  1. Shailesh

    hey Steve, I’m currently using Microsoft SQL Server 2017 (RTM-CU5). Suddenly since morning today sql server agent jobs started failing, I checked the job for maintenance is executing for over a day now and still not succeeded. I am not sure but guessing job failure is associated with your post because when I checked maintenance job history I could see same error. I checked the stored proc and it already has deallocate statement at the position you have mentioned. Can there be any other reason why my jobs are failing? TIA
    Shailesh

Leave a Reply

Related Articles