Search code examples
ssissql-server-agentsql-server-2017

SSIS Server Maintenance Job error

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.enter image description here

Solution

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