Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
: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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.