With SQL Server 2012 Microsoft introduced the SSIS Project Deployment system to better integrate SSIS with SQL Server.
With that comes a special database SSISDB that tracks packages and executions of packages.
There's a maintenance script that deletes old package history. cleanup_server_retention_window
This script depends on using ON DELETE CASCADE triggers to delete records at the top of a tree of tables and use those deletions to cascade down.
Where I have worked, I've found that when you run hundreds or thousands of executions of SSIS packages in a day this causes a problem.
The issue is that the deletion at the top table (internal.operations) as it cascades down through these tables the lock escalation will ultimately result in multiple table locks which will keep any other SSIS packages from executing.
--[internal].[operations]
-- [internal].[executions]
-- [internal].[executable_statistics]
-- [internal].[execution_component_phases]
-- [internal].[execution_data_statistics]
-- [internal].[execution_data_taps]
-- [internal].[execution_parameter_values]
-- [internal].[execution_property_override_values]
-- [internal].[extended_operation_info]
-- [internal].[operation_messages] --(DATA HEAVY)
-- [internal].[event_messages] --(DATA HEAVY)
-- [internal].[event_message_context]
-- [internal].[operation_os_sys_info]
-- [internal].[operation_permissions]
-- [internal].[validations]
I've seen a script that will erase the SSISDB's entire execution history via truncate.
http://thinknook.com/truncate-ssis-catalog-database-operation-log-tables-2012-11-03/
but this isn't great when you're only wanting to routinely purge history older than a given date.
So I came up with this... it's less set oriented than I would care for, but it keeps the lock escalation from table locks which will allow other SSIS jobs to execute.
It starts at the bottom tables and deletes rows from there in batches as specified by the @batch_size parameter, then works its way back up the tree.
You might want to play with @batch_size to find where your locking sweet spot is.
The @operations_divisor is what to divide your batch size by. This is because when you delete rows at that level, the FK Cascade will descend to every table in the chain, and may escallate locks there even though we've done everything we can to avoid that.
You'll also likely want to disable Lock Escalation on the SSISDB tables because this will still cascade down the FK chains, just after deleting the records from the lower level tables first.
Disable Lock Escalation
USE SSISDB
GO
DECLARE @SQL NVARCHAR(MAX)
SELECT
@SQL = COALESCE(@SQL + N'ALTER TABLE [' + SCHEMA_NAME(schema_id) + N'].[' + name + N'] SET (LOCK_ESCALATION=DISABLE);','ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name + '] SET (LOCK_ESCALATION=DISABLE);')
FROM
sys.tables
EXEC sp_executesql @SQL
Create some indexes to help reduce table contention and speed up cleanup
USE [SSISDB]
GO
/****** Object: Index [idx_operations_main] Script Date: 12/4/2015 9:34:36 AM ******/
CREATE NONCLUSTERED INDEX [idx_operations_main] ON [internal].[operations]
(
[end_time] ASC,
[created_time] ASC,
[status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [IX_ExecutableStatistics_Execution_id] Script Date: 12/4/2015 9:35:19 AM ******/
CREATE NONCLUSTERED INDEX [IX_ExecutableStatistics_Execution_id] ON [internal].[executable_statistics]
(
[execution_id] ASC
)
INCLUDE ( [statistics_id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [idx_execution_component_phases_execution_id] Script Date: 12/4/2015 9:35:52 AM ******/
CREATE NONCLUSTERED INDEX [idx_execution_component_phases_execution_id] ON [internal].[execution_component_phases]
(
[execution_id] ASC
)
INCLUDE ( [phase_stats_id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [idx_execution_data_statistics] Script Date: 12/4/2015 9:36:26 AM ******/
CREATE NONCLUSTERED INDEX [idx_execution_data_statistics] ON [internal].[execution_data_statistics]
(
[execution_id] ASC
)
INCLUDE ( [data_stats_id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [idx_execution_data_taps] Script Date: 12/4/2015 9:36:57 AM ******/
CREATE NONCLUSTERED INDEX [idx_execution_data_taps] ON [internal].[execution_data_taps]
(
[execution_id] ASC
)
INCLUDE ( [data_tap_id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
USE [SSISDB]
GO
/****** Object: Index [IX_ExecutionParameterValue_ExecutionId] Script Date: 12/4/2015 9:37:23 AM ******/
CREATE NONCLUSTERED INDEX [IX_ExecutionParameterValue_ExecutionId] ON [internal].[execution_parameter_values]
(
[execution_id] ASC
)
INCLUDE ( [object_type],
[parameter_data_type],
[parameter_name],
[parameter_value],
[sensitive_parameter_value],
[base_data_type],
[sensitive],
[required],
[value_set],
[runtime_override])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [IX_ExecutionParameterValue_ExecutionId2] Script Date: 12/4/2015 9:37:38 AM ******/
CREATE NONCLUSTERED INDEX [IX_ExecutionParameterValue_ExecutionId2] ON [internal].[execution_parameter_values]
(
[parameter_name] ASC
)
INCLUDE ( [execution_id],
[object_type],
[parameter_data_type],
[parameter_value],
[sensitive_parameter_value],
[base_data_type],
[sensitive],
[required],
[value_set],
[runtime_override])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [idx_execution_property_override_values] Script Date: 12/4/2015 9:38:08 AM ******/
CREATE NONCLUSTERED INDEX [idx_execution_property_override_values] ON [internal].[execution_property_override_values]
(
[execution_id] ASC
)
INCLUDE ( [property_id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [idx_extended_operation_info] Script Date: 12/4/2015 9:38:48 AM ******/
CREATE NONCLUSTERED INDEX [idx_extended_operation_info] ON [internal].[extended_operation_info]
(
[operation_id] ASC
)
INCLUDE ( [info_id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [IX_OperationMessages_Operation_id] Script Date: 12/4/2015 9:39:22 AM ******/
CREATE NONCLUSTERED INDEX [IX_OperationMessages_Operation_id] ON [internal].[operation_messages]
(
[operation_id] ASC
)
INCLUDE ( [operation_message_id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [IX_EventMessages_Operation_id] Script Date: 12/4/2015 9:40:08 AM ******/
CREATE NONCLUSTERED INDEX [IX_EventMessages_Operation_id] ON [internal].[event_messages]
(
[operation_id] ASC,
[event_message_id] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [IX_EventMessageContext_Operation_id] Script Date: 12/4/2015 9:40:34 AM ******/
CREATE NONCLUSTERED INDEX [IX_EventMessageContext_Operation_id] ON [internal].[event_message_context]
(
[event_message_id] ASC,
[operation_id] ASC
)
INCLUDE ( [context_id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [idx_operations_os_sys_info] Script Date: 12/4/2015 9:41:04 AM ******/
CREATE NONCLUSTERED INDEX [idx_operations_os_sys_info] ON [internal].[operation_os_sys_info]
(
[operation_id] ASC
)
INCLUDE ( [info_id],
[total_physical_memory_kb],
[available_physical_memory_kb],
[total_page_file_kb],
[available_page_file_kb],
[cpu_count])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [IX_OperationMessages_Operation_id] Script Date: 12/4/2015 9:41:33 AM ******/
CREATE NONCLUSTERED INDEX [IX_OperationMessages_Operation_id] ON [internal].[operation_messages]
(
[operation_id] ASC
)
INCLUDE ( [operation_message_id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [IX_EventMessages_Operation_id2] Script Date: 12/4/2015 3:05:48 PM ******/
CREATE NONCLUSTERED INDEX [IX_EventMessages_Operation_id2] ON [internal].[event_messages]
(
[event_message_id] ASC,
[operation_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [IX_EventMessageContext_Operation_id2] Script Date: 12/4/2015 3:07:40 PM ******/
CREATE NONCLUSTERED INDEX [IX_EventMessageContext_Operation_id2] ON [internal].[event_message_context]
(
[operation_id] ASC,
[event_message_id] ASC
)
INCLUDE ( [context_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [IX_EventMessageContext_Operation_id] Script Date: 12/4/2015 3:08:11 PM ******/
CREATE NONCLUSTERED INDEX [IX_EventMessageContext_Operation_id] ON [internal].[event_message_context]
(
[event_message_id] ASC,
[operation_id] ASC
)
INCLUDE ( [context_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [IX_EventMessages_Operation_id] Script Date: 12/4/2015 3:09:11 PM ******/
CREATE NONCLUSTERED INDEX [IX_EventMessages_Operation_id] ON [internal].[event_messages]
(
[operation_id] ASC,
[event_message_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [IX_EventMessages_Operation_id2] Script Date: 12/4/2015 3:09:39 PM ******/
CREATE NONCLUSTERED INDEX [IX_EventMessages_Operation_id2] ON [internal].[event_messages]
(
[event_message_id] ASC,
[operation_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
Replace the call to [internal].[cleanup_server_retention_window] in your SSIS DB Maintenance job in SQL Agent with a call to this.
Replacement Procedure for internal.cleanup_server_retention_window
USE [SSISDB]
GO
/****** Object: StoredProcedure [internal].[cleanup_server_retention_window_bottomup] Script Date: 12/3/2015 6:32:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***********************************************************************
Procedure: [internal].[cleanup_server_retention_window_bottomup]
Author: Mark Tassin
Date: 2013-05-14
Purpose: To control deletions from the SSISDB operational records in a way that
doesn't kill SQL Server performance
Parameters:
@batch_size int The number of records to delete in a givne batch
@retention_days int The number of days of records to retain
@operations_divisor int Divides the @batch_size by this number to reduce the
rows deleted at the internal.operations level because
deletions there cascade to all the tables below.
Documentation:
--[internal].[operations]
-- [internal].[executions]
-- [internal].[executable_statistics]
-- [internal].[execution_component_phases]
-- [internal].[execution_data_statistics]
-- [internal].[execution_data_taps]
-- [internal].[execution_parameter_values]
-- [internal].[execution_property_override_values]
-- [internal].[extended_operation_info]
-- [internal].[operation_messages] --(DATA HEAVY)
-- [internal].[event_messages] --(DATA HEAVY)
-- [internal].[event_message_context]
-- [internal].[operation_os_sys_info]
-- [internal].[operation_permissions]
-- [internal].[validations]
***********************************************************************/
CREATE PROCEDURE [internal].[cleanup_server_retention_window_bottomup]
@batch_size int = 50000,
@retention_window_length int = 15,
@operations_divisor INT = 100
AS
SET NOCOUNT ON
DECLARE @Rows_Affected INT=1
DECLARE @Temp_Date DATETIMEOFFSET = DATEADD(dd,-@retention_window_length,SYSDATETIMEOFFSET())
--Internal.event_message_context
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) a
FROM
internal.event_message_context a WITH (ROWLOCK)
JOIN internal.operations d ON
a.operation_id = d.operation_id
WHERE d.[end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) a
FROM
internal.event_message_context a WITH (ROWLOCK)
JOIN internal.operations d ON
a.operation_id = d.operation_id
WHERE
d.[end_time] IS NULL AND d.[status] = 1 AND d.[created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
--internal.event_messages
SET @Rows_Affected = 1
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) b
FROM
internal.event_messages b WITH (ROWLOCK)
JOIN internal.operations d ON
b.operation_id = d.operation_id
WHERE d.[end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) b
FROM
internal.event_messages b WITH (ROWLOCK)
JOIN internal.operations d ON
b.operation_id = d.operation_id
WHERE
d.[end_time] IS NULL AND d.[status] = 1 AND d.[created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
--internal.operation_messages
SET @Rows_Affected = 1
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) c
FROM
internal.operation_messages c WITH (ROWLOCK) JOIN internal.operations d ON
c.operation_id = d.operation_id
WHERE d.[end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) c
FROM
internal.operation_messages c WITH (ROWLOCK) JOIN internal.operations d ON
c.operation_id = d.operation_id
WHERE
d.[end_time] IS NULL AND d.[status] = 1 AND d.[created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
--internal.extended_operation_info
SET @Rows_Affected=1
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) a
FROM
internal.extended_operation_info a WITH (ROWLOCK) JOIN internal.operations b ON
a.operation_id = b.operation_id
WHERE b.[end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) a
FROM
internal.extended_operation_info a WITH (ROWLOCK) JOIN internal.operations b ON
a.operation_id = b.operation_id
WHERE
b.[end_time] IS NULL AND b.[status] = 1 AND b.[created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
--internal.opeation_os_sys_info
SET @Rows_Affected=1
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) a
FROM
[internal].[operation_os_sys_info] a WITH (ROWLOCK) JOIN internal.operations b ON
a.operation_id = b.operation_id
WHERE b.[end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) a
FROM
[internal].[operation_os_sys_info] a WITH (ROWLOCK) JOIN internal.operations b ON
a.operation_id = b.operation_id
WHERE
b.[end_time] IS NULL AND b.[status] = 1 AND b.[created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
--internal.operation_permissions
SET @Rows_Affected=1
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) a
FROM
[internal].[operation_permissions] a WITH (ROWLOCK) JOIN internal.operations b ON
a.object_id = b.operation_id
WHERE b.[end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) a
FROM
[internal].[operation_permissions] a WITH (ROWLOCK) JOIN internal.operations b ON
a.object_id = b.operation_id
WHERE
b.[end_time] IS NULL AND b.[status] = 1 AND b.[created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
--internal.validations
SET @Rows_Affected=1
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) a
FROM
[internal].[validations] a WITH (ROWLOCK) JOIN internal.operations b ON
a.validation_id = b.operation_id
WHERE b.[end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) a
FROM
[internal].[validations] a WITH (ROWLOCK) JOIN internal.operations b ON
a.validation_id = b.operation_id
WHERE
b.[end_time] IS NULL AND b.[status] = 1 AND b.[created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
--internal.executable_statistics
SET @Rows_Affected=1
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) a
FROM
internal.executable_statistics a WITH (ROWLOCK)
JOIN internal.operations c ON
a.execution_id = c.operation_id
WHERE c.[end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) a
FROM
internal.executable_statistics a WITH (ROWLOCK)
JOIN internal.operations c ON
a.execution_id = c.operation_id
WHERE
c.[end_time] IS NULL AND c.[status] = 1 AND c.[created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
--[internal].[execution_component_phases]
SET @Rows_Affected=1
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) a
FROM
[internal].[execution_component_phases] a WITH (ROWLOCK)
JOIN internal.operations c ON
a.execution_id = c.operation_id
WHERE c.[end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) a
FROM
[internal].[execution_component_phases] a WITH (ROWLOCK)
JOIN internal.operations c ON
a.execution_id = c.operation_id
WHERE
c.[end_time] IS NULL AND c.[status] = 1 AND c.[created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
--[internal].[execution_data_statistics]
SET @Rows_Affected=1
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) a
FROM
[internal].[execution_data_statistics] a WITH (ROWLOCK)
JOIN internal.operations c ON
a.execution_id = c.operation_id
WHERE c.[end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) a
FROM
[internal].[execution_data_statistics] a WITH (ROWLOCK)
JOIN internal.operations c ON
a.execution_id = c.operation_id
WHERE
c.[end_time] IS NULL AND c.[status] = 1 AND c.[created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
--[internal].[execution_data_taps]
SET @Rows_Affected=1
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) a
FROM
[internal].[execution_data_taps] a WITH (ROWLOCK)
JOIN internal.operations c ON
a.execution_id = c.operation_id
WHERE c.[end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) a
FROM
[internal].[execution_data_taps] a WITH (ROWLOCK)
JOIN internal.operations c ON
a.execution_id = c.operation_id
WHERE
c.[end_time] IS NULL AND c.[status] = 1 AND c.[created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
--[internal].[execution_parameter_values]
SET @Rows_Affected=1
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) a
FROM
[internal].[execution_parameter_values] a WITH (ROWLOCK)
JOIN internal.operations c ON
a.execution_id = c.operation_id
WHERE c.[end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) a
FROM
[internal].[execution_parameter_values] a WITH (ROWLOCK)
JOIN internal.operations c ON
a.execution_id = c.operation_id
WHERE
c.[end_time] IS NULL AND c.[status] = 1 AND c.[created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
--[internal].[execution_property_override_values]
SET @Rows_Affected=1
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) a
FROM
[internal].[execution_property_override_values] a WITH (ROWLOCK)
JOIN internal.operations c ON
a.execution_id = c.operation_id
WHERE c.[end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) a
FROM
[internal].[execution_property_override_values] a WITH (ROWLOCK)
JOIN internal.operations c ON
a.execution_id = c.operation_id
WHERE
c.[end_time] IS NULL AND c.[status] = 1 AND c.[created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
--internal.executions
SET @Rows_Affected=1
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) b
FROM
internal.executions b WITH (ROWLOCK) JOIN internal.operations c ON
b.execution_id = c.operation_id
WHERE c.[end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) b
FROM
internal.executions b WITH (ROWLOCK) JOIN internal.operations c ON
b.execution_id = c.operation_id
WHERE
c.[end_time] IS NULL AND c.[status] = 1 AND c.[created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
--we're going to reduce the batch size here because deleting rows from this table
--will cascade into all of the tables below.
SET @batch_size = @batch_size / @operations_divisor
--internal.operations
SET @Rows_Affected=1
WHILE @Rows_Affected > 0
BEGIN
DELETE TOP(@batch_size) c
FROM
internal.operations c WITH (ROWLOCK)
WHERE [end_time] <= @temp_date
SELECT @Rows_Affected = @@ROWCOUNT
DELETE TOP(@batch_size) c
FROM
internal.operations c WITH (ROWLOCK)
WHERE
[end_time] IS NULL AND [status] = 1 AND [created_time] <= @temp_date
SELECT @Rows_Affected += @@ROWCOUNT
END
Thanks to Ben Kubicek to referencing this article. http://www.sqlservercentral.com/articles/SSISDB/138330/
Based on that, we'll also grant execute on my proc to our job owner. Wasn't an issue for me, as I've pretty much made sa own all of my standard maintenance agent jobs, but for those of you who need it.
GRANT EXECUTE ON internal.cleanup_server_retention_window_bottomup TO [##MS_SSISServerCleanupJobUser##]
I personally changed my job's execution to just specify the retention window, mostly because getting to that configuration setting to confirm my values match didn't matter to me, but Ben also has a good point, if you want that configuration value to drive your retention window, add this bit of code.
declare @retention_window_length int
select @retention_window_length = cast(property_value as int) from [catalog].[catalog_properties] where property_name = 'RETENTION_WINDOW'
exec [internal].[cleanup_server_retention_window_bottomup] @retention_window_length = @retention_window_length
SSIS_Maintenance_Deploy.zip
Friday, December 4. 2015