none
SSISDB (2017 CU8) running in Always on Group - Maintenance procedure bug (cleanup_server_retention_window)? RRS feed

  • Question

  • Hi,

    Since upgrading to SQL 2017 (from 2017), the SSIS maintenance job keeps failing.  The job step is the following..

      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]

    However it fails with the error message"A cursor with the name 'execution_cursor' does not exist."

    After looking through the proc, it de-allocated the cursor then tries to access it again within the while loop.  So we are deleting 1000 records from the SSIS history at a time, but then hit the error.  Whilst I could modify the procedure, I thought i'd see if anyone else encounted this.

    SET QUOTED_IDENTIFIER ON
    SET ANSI_NULLS ON
    GO
    
    
    CREATE PROCEDURE [internal].[cleanup_server_retention_window]
    WITH EXECUTE AS 'AllSchemaOwner'
    AS
        SET NOCOUNT ON
        
        DECLARE @enable_clean_operation bit
        DECLARE @retention_window_length int
        DECLARE @server_operation_encryption_level int
        
        DECLARE @caller_name nvarchar(256)
        DECLARE @caller_sid  varbinary(85)
        DECLARE @operation_id bigint
        
        EXECUTE AS CALLER
            SET @caller_name =  SUSER_NAME()
            SET @caller_sid =   SUSER_SID()
        REVERT
             
        
        BEGIN TRY
            SELECT @enable_clean_operation = CONVERT(bit, property_value) 
                FROM [catalog].[catalog_properties]
                WHERE property_name = 'OPERATION_CLEANUP_ENABLED'
            
            IF @enable_clean_operation = 1
            BEGIN
                SELECT @retention_window_length = CONVERT(int,property_value)  
                    FROM [catalog].[catalog_properties]
                    WHERE property_name = 'RETENTION_WINDOW'
                    
    
                IF @retention_window_length <= 0 
                BEGIN
                    RAISERROR(27163    ,16,1,'RETENTION_WINDOW')
                END
                
                SELECT @server_operation_encryption_level = CONVERT(int,property_value)  
                    FROM [catalog].[catalog_properties]
                    WHERE property_name = 'SERVER_OPERATION_ENCRYPTION_LEVEL'
    
                IF @server_operation_encryption_level NOT in (1, 2)       
                BEGIN
                    RAISERROR(27163    ,16,1,'SERVER_OPERATION_ENCRYPTION_LEVEL')
                END
                INSERT INTO [internal].[operations] (
                    [operation_type],  
                    [created_time], 
                    [object_type],
                    [object_id],
                    [object_name],
                    [status], 
                    [start_time],
                    [caller_sid], 
                    [caller_name]
                    )
                VALUES (
                    2,
                    SYSDATETIMEOFFSET(),
                    NULL,                     
                    NULL,                     
                    NULL,                     
                    1,      
                    SYSDATETIMEOFFSET(),
                    @caller_sid,            
                    @caller_name            
                    ) 
                SET @operation_id = SCOPE_IDENTITY() 
                
                DECLARE @temp_date datetimeoffset
                DECLARE @rows_affected bigint
                DECLARE @delete_batch_size int
    
                
                SET @delete_batch_size = 1000  
                SET @rows_affected = @delete_batch_size
                
                SET @temp_date = DATEADD(day, -@retention_window_length, SYSDATETIMEOFFSET())
                
                CREATE TABLE #deleted_ops (operation_id bigint, operation_type smallint)
                DECLARE execution_cursor CURSOR GLOBAL FOR SELECT operation_id FROM #deleted_ops  WHERE operation_type = 200
    
    			DECLARE @sqlString_operation_messages_scaleout   nvarchar(1024)
                DECLARE @sqlString_event_messages_scaleout       nvarchar(1024)
                DECLARE @sqlString_event_message_context_scaleout        nvarchar(1024)
    
                IF @server_operation_encryption_level = 1
                BEGIN
                    DECLARE @execution_id bigint
                    DECLARE @sqlString              nvarchar(1024)
                    DECLARE @sqlString_cert         nvarchar(1024)
                    DECLARE @key_name               [internal].[adt_name]
                    DECLARE @certificate_name       [internal].[adt_name]
    
                WHILE (@rows_affected = @delete_batch_size)
                BEGIN
                    DELETE TOP (@delete_batch_size)
                        FROM [internal].[operations] 
                            OUTPUT DELETED.operation_id, DELETED.operation_type INTO #deleted_ops
                        WHERE ( [end_time] <= @temp_date
                        
                        OR ([end_time] IS NULL AND [status] = 1 AND [created_time] <= @temp_date ))
                        
                    SET @rows_affected = @@ROWCOUNT
                
                OPEN execution_cursor
                FETCH NEXT FROM execution_cursor INTO @execution_id
                
                WHILE @@FETCH_STATUS = 0
                BEGIN
                    SET @key_name = 'MS_Enckey_Exec_'+CONVERT(varchar,@execution_id)
                    SET @certificate_name = 'MS_Cert_Exec_'+CONVERT(varchar,@execution_id)
                    SET @sqlString_operation_messages_scaleout = 'delete from [internal].[operation_messages_scaleout] where operation_id = '+CONVERT(varchar,@execution_id)
                    SET @sqlString_event_messages_scaleout = 'delete from [internal].[event_messages_scaleout] where operation_id = '+CONVERT(varchar,@execution_id)
                    SET @sqlString_event_message_context_scaleout  = 'delete from [internal].[event_message_context_scaleout] where operation_id = '+CONVERT(varchar,@execution_id)
                            SET @sqlString = 'DROP SYMMETRIC KEY '+ @key_name
                            SET @sqlString_cert = 'DROP CERTIFICATE '+ @certificate_name
                            
                            BEGIN TRY
                        EXECUTE sp_executesql @sqlString
                                EXECUTE sp_executesql @sqlString_cert
                                EXECUTE sp_executesql @sqlString_operation_messages_scaleout
                                EXECUTE sp_executesql @sqlString_event_messages_scaleout
                                EXECUTE sp_executesql @sqlString_event_message_context_scaleout
                            END TRY
    
                            BEGIN CATCH
                                
                            END CATCH
    
                    FETCH NEXT FROM execution_cursor INTO @execution_id
                END
                CLOSE execution_cursor
                        TRUNCATE TABLE #deleted_ops
                    END
                    DROP TABLE #deleted_ops
    
                DEALLOCATE execution_cursor
                END
                ELSE BEGIN
                    WHILE (@rows_affected = @delete_batch_size)
                    BEGIN
                        DELETE TOP (@delete_batch_size)
                            FROM [internal].[operations] 
                             OUTPUT DELETED.operation_id, DELETED.operation_type INTO #deleted_ops
                            WHERE ( [end_time] <= @temp_date
                            OR ([end_time] IS NULL AND [status] = 1 AND [created_time] <= @temp_date ))
                        SET @rows_affected = @@ROWCOUNT
    
                          OPEN execution_cursor
                FETCH NEXT FROM execution_cursor INTO @execution_id
                 WHILE @@FETCH_STATUS = 0
                BEGIN
                    SET @sqlString_operation_messages_scaleout = 'delete from [internal].[operation_messages_scaleout] where operation_id = '+CONVERT(varchar,@execution_id)
                    SET @sqlString_event_messages_scaleout = 'delete from [internal].[event_messages_scaleout] where operation_id = '+CONVERT(varchar,@execution_id)
                    SET @sqlString_event_message_context_scaleout  = 'delete from [internal].[event_message_context_scaleout] where operation_id = '+CONVERT(varchar,@execution_id)
                    BEGIN TRY
                        EXECUTE sp_executesql @sqlString_operation_messages_scaleout
                        EXECUTE sp_executesql @sqlString_event_messages_scaleout
                        EXECUTE sp_executesql @sqlString_event_message_context_scaleout
                    END TRY
                    BEGIN CATCH 
                    END CATCH
                    FETCH NEXT FROM execution_cursor INTO @execution_id
                END
                CLOSE execution_cursor
                        TRUNCATE TABLE #deleted_ops
                DEALLOCATE execution_cursor
                   END
                    DROP TABLE #deleted_ops
                END
                
                UPDATE [internal].[operations]
                    SET [status] = 7,
                    [end_time] = SYSDATETIMEOFFSET()
                    WHERE [operation_id] = @operation_id                                  
            END
        END TRY
        BEGIN CATCH
            
            
            IF @server_operation_encryption_level = 1
            BEGIN
            IF (CURSOR_STATUS('local', 'execution_cursor') = 1 
                OR CURSOR_STATUS('local', 'execution_cursor') = 0)
            BEGIN
                CLOSE execution_cursor
                DEALLOCATE execution_cursor            
            END
            END
            
            UPDATE [internal].[operations]
                SET [status] = 4,
                [end_time] = SYSDATETIMEOFFSET()
                WHERE [operation_id] = @operation_id;       
            THROW
        END CATCH
        
        RETURN 0
    
    GO
    

    Wednesday, August 8, 2018 9:32 AM

All replies

  • Hi chris-walton.co.uk,

    So, does this issue occur before installing the SQL Server CU 8?

    Are there any other error message in job history or event views around the time?

    It's not recommend to modify the internal SP, so if the issue only happens in CU 8, you may try to uninstall the CU 8 temporarily and submit this issue to SQL Server feedback

    Or try to install SQL SERVER 2017 CU 9.

    Check if this issue persist.

    Regards,

    Pirlo Zhang 


    MSDN Community Support&lt;br/&gt; Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact &lt;a href=&quot;mailto:MSDNFSF@microsoft.com&quot;&gt;MSDNFSF@microsoft.com&lt;/a&gt;.

    Thursday, August 9, 2018 6:56 AM
    Moderator
  • Hi Pirlo,

    I'm having the same issue as @chris-walton.co.uk. I'm running SQL Server 2017 with the latest CU11 update. As cleanup_server_retention_window is an internal SP, i would prefer not to modify it manually. Do you have any suggestions on how to rectify this issue?

    Thanks.

    Monday, October 8, 2018 10:22 PM
  • It was something to do with the user not having permissions to create a global cursor from memory. I've asked the guy who found the issue to post exactly what it was. Soultion should be on here later today. 
    Tuesday, October 9, 2018 7:10 AM
  • Hi, Chris.

    Did you receive any more information about this? I am on SQL 2017 CU12 with the same issue and would rather not change internal procedures.

    I cannot find any official word from MS on the web about it either, nor is it mentioned in SQL 2017 CU13 which is the latest.

    Monday, February 25, 2019 8:10 AM
  • Hello,

    Has anyone found a solution to this issue?  We are on SQL Server 2017 CU12 and it is still occurring.

    Thanks.


    • Edited by MGDDba Friday, March 1, 2019 7:27 PM
    Friday, March 1, 2019 6:50 PM
  • Friday, April 12, 2019 5:36 PM
  • Hi Chris,

    it's a bug.The problem occurs only when you have more than 1000 rows and the procedure close and deallocate the cursor inside the while loop.

    I solved moving

    - OPEN execution_cursor   just after the declaration of the execution_cursor

    -   CLOSE execution_cursor and  DEALLOCATE execution_cursor   just before  the update

      UPDATE [internal].[operations]  
                    SET [status] = 7,  
                    [end_time] = SYSDATETIMEOFFSET()  
                    WHERE [operation_id] = @operation_id    

    • Proposed as answer by mauroBas82 Monday, August 26, 2019 8:37 AM
    Monday, August 26, 2019 8:34 AM