SSIS Server Maintenance Job error

Question

I receive following error message in sql server 2017 agent jobs for Maintenance job that is responsible for deleting old execution logs:

Date        5/23/2018 12:18:45 AM
Log     Job History (SSIS Server Maintenance Job)

Step ID     1
Server      KARABL3
Job Name        SSIS Server Maintenance Job
Step Name       SSIS Server Operation Records Maintenance
Duration        00:02:34
Sql Severity    16
Sql Message ID  16916
Operator Emailed    
Operator Net sent   
Operator Paged  
Retries Attempted   3

Message
Executed as user: ##MS_SSISServerCleanupJobLogin##. A cursor with the name 'execution_cursor' does not exist. [SQLSTATE 34000] (Error 16916).  NOTE: The step was retried the requested number of times (3) without succeeding.  The step failed.

After a while when logs count exceeded normal count it causes all jobs get fail because of time out.

the issue still remains. any idea? – VahidSH Aug 07 '18 at 13:33 — VahidSH, Aug 07 '18 at 13:33

Megadest · Accepted Answer · 2020-03-04T00:00:11.787

This is actually a bug introduced in SQL Server 2017.

See stored procedure SSISDB.[internal].[cleanup_server_retention_window].

Line 175 has DEALLOCATE execution_cursor

This statement needs to be moved one line lower, after END statement of the WHILE loop. It should be executed together with DROP TABLE #deleted_ops, not with TRUNCATE...

While the number of rows to delete is below "batch size" (1000) the "buggy" sproc actually runs OK, as it never needs to go into the loop twice, but if you have many executions to clean up, or have just reduced your retention period -- then it hits the wall.

score 1 · Answer 2 · answered May 30 '18 at 10:37

Disclaimer: I am one of the Vahid's colleagues.

Obviously, the error message doesn't provide any useful information.

Here is the first step of SSIS Server Maintenance Job.

DECLARE @role INT

SET @role = (
        SELECT [role]
        FROM [sys].[dm_hadr_availability_replica_states] hars
        INNER JOIN [sys].[availability_databases_cluster] adc ON hars.[group_id] = adc.[group_id]
        WHERE hars.[is_local] = 1
            AND adc.[database_name] = 'SSISDB'
        )

IF DB_ID('SSISDB') IS NOT NULL
    AND (
        @role IS NULL
        OR @role = 1
        )
    EXEC [SSISDB].[internal].[cleanup_server_retention_window]

At first, I thought maybe the [cleanup_server_retention_window] has been updated in the latest version of SQL server and that's causing the error. But, it wasn't the case.

Then I searched about cleanup_server_retention_window failing and this link came up. Several users suggested switching the recovery mode of the SSISDB to simple could solve the problem and it worked in this case as well. So, if you are facing a similar issue, I would suggest you to do the same.

SSIS Server Maintenance Job error

2 Answers2