SSISDB_cleardown.sql

:setvar SSISDB_NAME "SSISDb"
 
 
USE $(SSISDB_NAME)
 
SET NOCOUNT ON
SET XACT_ABORT ON
 
BEGIN TRANSACTION
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 1  
 
PRINT 'Truncating [internal].[event_message_context] table...'
 
TRUNCATE TABLE internal.event_message_context
--SELECT COUNT(context_id) FROM internal.event_message_context
 
PRINT 'Truncated [internal].[event_message_context] table successfully'
 
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 2
 
PRINT 'Truncating [internal].[event_messages] table...'
PRINT 'Removing Foreign Key Constraint [FK_EventMessageContext_EventMessageId_EventMessages]...'
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_EventMessageContext_EventMessageId_EventMessages')
    ALTER TABLE [internal].[event_message_context] DROP CONSTRAINT [FK_EventMessageContext_EventMessageId_EventMessages]
 
 
TRUNCATE TABLE internal.event_messages
--SELECT COUNT(event_message_id) FROM internal.event_messages
 
ALTER TABLE [internal].[event_message_context]  WITH CHECK ADD  CONSTRAINT [FK_EventMessageContext_EventMessageId_EventMessages] FOREIGN KEY([event_message_id])
REFERENCES [internal].[event_messages] ([event_message_id])
ON DELETE CASCADE
GO
 
ALTER TABLE [internal].[event_message_context] CHECK CONSTRAINT [FK_EventMessageContext_EventMessageId_EventMessages]
GO
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_EventMessageContext_EventMessageId_EventMessages')
    PRINT 'Foreign Key Constraint [FK_EventMessageContext_EventMessageId_EventMessages] re-added successfully...'
ELSE
    RAISERROR('Error adding foreign Key Constraint [FK_EventMessageContext_EventMessageId_EventMessages]',16,1)
 
PRINT 'Truncated [internal].[event_messages] table successfully'
 
 
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 3
 
PRINT 'Truncating [internal].[operations_messages] table...'
PRINT 'Removing Foreign Key Constraint [FK_EventMessages_OperationMessageId_OperationMessage]...'
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_EventMessages_OperationMessageId_OperationMessage')
    ALTER TABLE [internal].[event_messages] DROP CONSTRAINT [FK_EventMessages_OperationMessageId_OperationMessage]
 
 
TRUNCATE TABLE internal.operation_messages
--SELECT COUNT(operation_message_id) FROM internal.operation_messages
 
ALTER TABLE [internal].[event_messages]  WITH CHECK ADD  CONSTRAINT [FK_EventMessages_OperationMessageId_OperationMessage] FOREIGN KEY([event_message_id])
REFERENCES [internal].[operation_messages] ([operation_message_id])
ON DELETE CASCADE
GO
 
ALTER TABLE [internal].[event_messages] CHECK CONSTRAINT [FK_EventMessages_OperationMessageId_OperationMessage]
GO
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_EventMessages_OperationMessageId_OperationMessage')
    PRINT 'Foreign Key Constraint [FK_EventMessages_OperationMessageId_OperationMessage] re-added successfully...'
ELSE
    RAISERROR('Error adding foreign Key Constraint [FK_EventMessages_OperationMessageId_OperationMessage]',16,1)
 
PRINT 'Truncated [internal].[operation_messages] table successfully'
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 4
 
PRINT 'Truncating [internal].[validations] table...'
 
TRUNCATE TABLE internal.validations
--SELECT COUNT(Validation_Id) FROM internal.validations
 
PRINT 'Truncated [internal].[validations] table successfully'
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 5
 
PRINT 'Truncating [internal].[operation_permissions] table...'
 
TRUNCATE TABLE internal.operation_permissions
--SELECT COUNT(Id) FROM internal.operation_permissions
 
PRINT 'Truncated [internal].[operation_permissions] table successfully'
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 6
 
PRINT 'Truncating [internal].[extended_operation_info] table...'
 
TRUNCATE TABLE internal.extended_operation_info
--SELECT COUNT(Info_Id) FROM internal.extended_operation_info
 
PRINT 'Truncated [internal].[extended_operation_info] table successfully'
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 7
 
PRINT 'Truncating [internal].[operation_os_sys_info] table...'
 
TRUNCATE TABLE internal.operation_os_sys_info
--SELECT COUNT(Info_Id) FROM internal.operation_os_sys_info
 
PRINT 'Truncating [internal].[operation_os_sys_info] table successfully'
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 8
 
PRINT 'Truncating [internal].[executable_statistics] table...'
 
TRUNCATE TABLE internal.executable_statistics
--SELECT COUNT(statistics_id) FROM internal.executable_statistics
 
PRINT 'Truncated [internal].[executable_statistics] table successfully'
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 9
 
PRINT 'Truncating [internal].[execution_data_taps] table...'
 
TRUNCATE TABLE internal.execution_data_taps
--SELECT COUNT(data_tap_id) FROM internal.execution_data_taps
 
PRINT 'Truncated [internal].[execution_data_taps] table successfully'
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 10
 
PRINT 'Truncating [internal].[execution_property_override_values] table...'
 
TRUNCATE TABLE [internal].[execution_property_override_values]
--SELECT COUNT(property_id) FROM [internal].[execution_property_override_values]
 
PRINT 'Truncated [internal].[execution_property_override_values] table successfully'
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 11
 
 
PRINT 'Truncating [internal].[execution_data_statistics] table...'
 
TRUNCATE TABLE [internal].[execution_data_statistics]
--SELECT COUNT(data_stats_id) FROM [internal].[execution_data_statistics]
 
PRINT 'Truncated [internal].[execution_data_statistics] table successfully'
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 12
 
PRINT 'Truncating [internal].[execution_parameter_values] table...'
 
TRUNCATE TABLE [internal].[execution_parameter_values]
--SELECT COUNT(execution_parameter_id) FROM [internal].[execution_parameter_values]
 
PRINT 'Truncated [internal].[execution_parameter_values] table successfully'
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 13
 
PRINT 'Truncating [internal].[execution_component_phases] table...'
 
TRUNCATE TABLE [internal].[execution_component_phases]
--SELECT COUNT(phase_stats_id) FROM [internal].[execution_component_phases]
 
PRINT 'Truncated [internal].[execution_component_phases] table successfully'
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 14
 
PRINT 'Truncating [internal].[executions] table...'
 
PRINT 'Removing Foreign Key Constraint [FK_ExecCompPhases_ExecutionId_Executions]...'
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_ExecCompPhases_ExecutionId_Executions')
    ALTER TABLE [internal].[execution_component_phases] DROP CONSTRAINT [FK_ExecCompPhases_ExecutionId_Executions]
 
 
PRINT 'Removing Foreign Key Constraint [FK_ExecutionParameterValue_ExecutionId_Executions]...'
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_ExecutionParameterValue_ExecutionId_Executions')
    ALTER TABLE [internal].[execution_parameter_values] DROP CONSTRAINT [FK_ExecutionParameterValue_ExecutionId_Executions]
 
 
PRINT 'Removing Foreign Key Constraint [FK_ExecutableStatistics_ExecutionId_Executions]...'
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_ExecutableStatistics_ExecutionId_Executions')
    ALTER TABLE [internal].[executable_statistics] DROP CONSTRAINT [FK_ExecutableStatistics_ExecutionId_Executions]
 
 
PRINT 'Removing Foreign Key Constraint [FK_ExecutionPropertyOverrideValue_ExecutionId_Executions]...'
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_ExecutionPropertyOverrideValue_ExecutionId_Executions')
    ALTER TABLE [internal].[execution_property_override_values] DROP CONSTRAINT [FK_ExecutionPropertyOverrideValue_ExecutionId_Executions]
 
 
PRINT 'Removing Foreign Key Constraint [FK_ExecDataStat_ExecutionId_Executions]...'
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_ExecDataStat_ExecutionId_Executions')
    ALTER TABLE [internal].[execution_data_statistics] DROP CONSTRAINT [FK_ExecDataStat_ExecutionId_Executions]
 
 
PRINT 'Removing Foreign Key Constraint [FK_ExecDataTaps_ExecutionId_Executions]...'
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_ExecDataTaps_ExecutionId_Executions')
    ALTER TABLE [internal].[execution_data_taps] DROP CONSTRAINT [FK_ExecDataTaps_ExecutionId_Executions]
 
 
TRUNCATE TABLE [internal].[executions]
--SELECT COUNT(execution_id) FROM [internal].[executions]
 
 
ALTER TABLE [internal].[execution_component_phases]  WITH CHECK ADD  CONSTRAINT [FK_ExecCompPhases_ExecutionId_Executions] FOREIGN KEY([execution_id])
REFERENCES [internal].[executions] ([execution_id])
ON DELETE CASCADE
GO
 
ALTER TABLE [internal].[execution_component_phases] CHECK CONSTRAINT [FK_ExecCompPhases_ExecutionId_Executions]
GO
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_ExecCompPhases_ExecutionId_Executions')
    PRINT 'Foreign Key Constraint [FK_ExecCompPhases_ExecutionId_Executions] re-added successfully...'
 
 
ALTER TABLE [internal].[execution_parameter_values]  WITH CHECK ADD  CONSTRAINT [FK_ExecutionParameterValue_ExecutionId_Executions] FOREIGN KEY([execution_id])
REFERENCES [internal].[executions] ([execution_id])
ON DELETE CASCADE
GO
 
ALTER TABLE [internal].[execution_parameter_values] CHECK CONSTRAINT [FK_ExecutionParameterValue_ExecutionId_Executions]
GO
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_ExecutionParameterValue_ExecutionId_Executions')
    PRINT 'Foreign Key Constraint [FK_ExecutionParameterValue_ExecutionId_Executions] re-added successfully...'
 
 
 
ALTER TABLE [internal].[executable_statistics]  WITH CHECK ADD  CONSTRAINT [FK_ExecutableStatistics_ExecutionId_Executions] FOREIGN KEY([execution_id])
REFERENCES [internal].[executions] ([execution_id])
ON DELETE CASCADE
GO
 
ALTER TABLE [internal].[executable_statistics] CHECK CONSTRAINT [FK_ExecutableStatistics_ExecutionId_Executions]
GO
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_ExecutableStatistics_ExecutionId_Executions')
    PRINT 'Foreign Key Constraint [FK_ExecutableStatistics_ExecutionId_Executions] re-added successfully...'
ELSE
    RAISERROR('Error adding Foreign Key Constraint [FK_ExecutableStatistics_ExecutionId_Executions]',16,1)
 
ALTER TABLE [internal].[execution_property_override_values]  WITH CHECK ADD  CONSTRAINT [FK_ExecutionPropertyOverrideValue_ExecutionId_Executions] FOREIGN KEY([execution_id])
REFERENCES [internal].[executions] ([execution_id])
ON DELETE CASCADE
GO
 
ALTER TABLE [internal].[execution_property_override_values] CHECK CONSTRAINT [FK_ExecutionPropertyOverrideValue_ExecutionId_Executions]
GO
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_ExecutionPropertyOverrideValue_ExecutionId_Executions')
    PRINT 'Foreign Key Constraint [FK_ExecutionPropertyOverrideValue_ExecutionId_Executions] re-added successfully...'
ELSE
    RAISERROR('Error adding Foreign Key Constraint [FK_ExecutionPropertyOverrideValue_ExecutionId_Executions]',16,1)
 
ALTER TABLE [internal].[execution_data_statistics]  WITH CHECK ADD  CONSTRAINT [FK_ExecDataStat_ExecutionId_Executions] FOREIGN KEY([execution_id])
REFERENCES [internal].[executions] ([execution_id])
ON DELETE CASCADE
GO
 
ALTER TABLE [internal].[execution_data_statistics] CHECK CONSTRAINT [FK_ExecDataStat_ExecutionId_Executions]
GO
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_ExecDataStat_ExecutionId_Executions')
    PRINT 'Foreign Key Constraint [FK_ExecDataStat_ExecutionId_Executions] re-added successfully...'
ELSE
    RAISERROR('Error adding Foreign Key Constraint [FK_ExecDataStat_ExecutionId_Executions]',16,1)
 
ALTER TABLE [internal].[execution_data_taps]  WITH CHECK ADD  CONSTRAINT [FK_ExecDataTaps_ExecutionId_Executions] FOREIGN KEY([execution_id])
REFERENCES [internal].[executions] ([execution_id])
ON DELETE CASCADE
GO
 
ALTER TABLE [internal].[execution_data_taps] CHECK CONSTRAINT [FK_ExecDataTaps_ExecutionId_Executions]
GO
 
IF EXISTS(SELECT 1 from sys.foreign_keys WHERE Name = 'FK_ExecDataTaps_ExecutionId_Executions')
    PRINT 'Foreign Key Constraint [FK_ExecDataTaps_ExecutionId_Executions] re-added successfully...'
ELSE
    RAISERROR('Error adding foreign Key Constraint [FK_ExecDataTaps_ExecutionId_Executions]',16,1)
 
 
PRINT 'Truncated [internal].[executions] table successfully'
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 14
 
DELETE FROM Internal.Operations
 
COMMIT TRANSACTION
 
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- STEP 15 
 
 
DBCC UPDATEUSAGE($(SSISDB_NAME))
 
EXEC sp_spaceused

----------------------------------------------------------------
 
--declare @fromdate DATETIME = DATEADD(DD,-30,GETDATE())
 
--SELECT count(*) FROM internal.operations where created_time <= @fromdate