SSISDB - delete execution record?

Question

Recently had some kind of a crash of SSIS on the production server. The cause was this error. After the crash and until server reboot, had messages in the SQL Server log every minute like this:

System.IO.FileLoadException: Could not load file or assembly 'microsoft.sqlserver.integrationservices.server...

Looking to fix this with the CLR suggested in that link. There's another problem: as a result all the SSIS packages which were scheduled while the problem persisted have ended up with rows in SSISDB.catalog.executions with status=1 ("Created").

This is just junk, which will get cleaned out in a couple of weeks. Problem is that one critical job starts by checking whether the package concerned is already (still) running. It does this like this:

SELECT execution_id FROM SSISDB.catalog.executions
WHERE
   package_name='The package name.dtsx' AND
   status IN (1,2,5,8) -- Created, Running, Pending, Stopping

If it finds anything, exits with error.

So obviously this failed after the server was rebooted, because there were execution records hanging about at status 1 (Created).

Is there a way to completely delete an SSISDB execution record, along with all its dependent rows? I've looked in the SSISDB stored procs (catalog and internal), but can't find anything.

You can update erroneous records, altering status value to 6 - " ended unexpectedly". It seems easier than deleting complete set of records. — Ferdipux, Jan 14 '21 at 07:15

score 1 · Accepted Answer · answered Jan 13 '21 at 15:59

Ultimately, they're just tables in the internal schema in SSISDB.

catalog.executions is defined as

CREATE VIEW [catalog].[executions]
AS
SELECT     execs.[execution_id], 
           execs.[folder_name], 
           execs.[project_name], 
           execs.[package_name],
           execs.[reference_id],
           execs.[reference_type], 
           execs.[environment_folder_name], 
           execs.[environment_name], 
           execs.[project_lsn], 
           execs.[executed_as_sid], 
           execs.[executed_as_name], 
           execs.[use32bitruntime],  
           opers.[operation_type], 
           opers.[created_time],  
           opers.[object_type], 
           opers.[object_id],
           opers.[status], 
           opers.[start_time], 
           opers.[end_time],  
           opers.[caller_sid], 
           opers.[caller_name], 
           opers.[process_id], 
           opers.[stopped_by_sid], 
           opers.[stopped_by_name],
           opers.[operation_guid] as [dump_id],
           opers.[server_name],
           opers.[machine_name],
           ossysinfos.[total_physical_memory_kb],
           ossysinfos.[available_physical_memory_kb],
           ossysinfos.[total_page_file_kb],
           ossysinfos.[available_page_file_kb],
           ossysinfos.[cpu_count]
FROM       [internal].[executions] execs INNER JOIN [internal].[operations] opers 
           ON execs.[execution_id]= opers.[operation_id]
           LEFT JOIN [internal].[operation_os_sys_info] ossysinfos
           ON ossysinfos.[operation_id]= execs.[execution_id]
WHERE      opers.[operation_id] in (SELECT id FROM [internal].[current_user_readable_operations])
           OR (IS_MEMBER('ssis_admin') = 1)
           OR (IS_SRVROLEMEMBER('sysadmin') = 1)
           OR (IS_MEMBER('ssis_logreader') = 1)

status comes from the internal.operations table, aliased as opers

Being lazy, I would just update the record there so a state to get it out of my way versus trying to track down all the nooks and crannies the child attributes are stored away.

update opers
-- The status of the operation. The possible values are 
-- created (1), running (2), canceled (3), failed (4), pending (5), 
-- ended unexpectedly (6), succeeded (7), stopping (8), and completed (9).
SET status = 6 /* or 9 */
FROM
    [internal].[operations] opers 
WHERE
    opers.operation_id = 0 /* your TODO use the correct operation_id/execution_id there */
    AND opers.status IN (1,2,5,8); -- Created, Running, Pending, Stopping

If you do want to go deleting, I think this is still relevant How can I clean up the SSISDB?

SSISDB - delete execution record?

1 Answers1