CODEWITHSUNDEEP
ssis
SebTHU
SebTHU

Reputation: 1405

SSISDB - delete execution record?

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.

Upvotes: 1

Views: 1369

Answers (1)

billinkc
billinkc

Reputation: 61239

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?

Upvotes: 1

Related Questions