SSISDB is full

 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.  

This is small example of SSIS DB. and sometime you will noticed this error when fail

"SSIS Server Maintenance Job".

Message

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

Missing Indexes for SQL Server

Missing Indexes for SQL Server 

SELECT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage], 

migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],

mid.equality_columns, mid.inequality_columns, mid.included_columns,

migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact

FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)

INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)

ON mig.index_handle = mid.index_handle

ORDER BY index_advantage DESC OPTION (RECOMPILE);