database 'SSISDB' is full due to 'ACTIVE_TRANSACTION'
You will noticed occasionally SSIS DB is full in your database server. or you may need to clean up SSIS DB in your SQL Server integration services.
MessageExecuted as user: ##MS_SSISServerCleanupJobLogin##. The transaction log for database 'SSISDB' is full due to 'ACTIVE_TRANSACTION'. [SQLSTATE 42000] (Error 9002). NOTE: The step was retried the requested number of times (3) without succeeding. The step failed.
FAQ,
How to clean up the SSISDB database using the script?
How to Delete data in SSISDB database?
SSIS Server Maintenance Job Fail. How to fixed?
Solution
1) Take full backup of SSIS database.
2) next clean some log and session tables as follows,
USE SSISDB
GO
--FK table
WHILE 1 = 1
BEGIN
DELETE TOP(1000) FROM [internal].[executions];
IF @@ROWCOUNT < 1000 BREAK;
END
GO
TRUNCATE TABLE [internal].[executable_statistics];
GO
TRUNCATE TABLE [internal].[execution_component_phases];GO
TRUNCATE TABLE [internal].[execution_data_statistics];GO
TRUNCATE TABLE [internal].[execution_data_taps];GO
TRUNCATE TABLE [internal].[execution_parameter_values];GO
TRUNCATE TABLE [internal].[execution_property_override_values];GO
TRUNCATE TABLE [internal].[extended_operation_info];GO
--FK table
WHILE 1 = 1
BEGIN
DELETE TOP(1000) FROM [internal].[operation_messages];
IF @@ROWCOUNT < 1000 BREAK;
END
GO
--FK table
WHILE 1 = 1
BEGIN
DELETE TOP(1000) FROM [internal].[event_messages];
IF @@ROWCOUNT < 1000 BREAK;
END
GO
TRUNCATE TABLE [internal].[event_message_context];GO
TRUNCATE TABLE [internal].[operation_os_sys_info];GO
TRUNCATE TABLE [internal].[operation_permissions];GO
TRUNCATE TABLE [internal].[validations];GO
USE msdb ;
GO
EXEC dbo.sp_start_job N'SSIS Server Maintenance Job';
GO
3)If needed you can shrink SSISDB database.