Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Schema Table RowCount
- internal event_message_context 1,869,028
- internal operation_messages 1,500,811
- internal event_messages 1,500,803
- USE SSISDB;
- SET NOCOUNT ON;
- IF object_id('tempdb..#DELETE_CANDIDATES') IS NOT NULL
- BEGIN
- DROP TABLE #DELETE_CANDIDATES;
- END;
- CREATE TABLE #DELETE_CANDIDATES
- (
- operation_id bigint NOT NULL PRIMARY KEY
- );
- DECLARE @DaysRetention int = 100;
- INSERT INTO
- #DELETE_CANDIDATES
- (
- operation_id
- )
- SELECT
- IO.operation_id
- FROM
- internal.operations AS IO
- WHERE
- IO.start_time < DATEADD(day, -@DaysRetention, CURRENT_TIMESTAMP);
- DELETE T
- FROM
- internal.event_message_context AS T
- INNER JOIN
- #DELETE_CANDIDATES AS DC
- ON DC.operation_id = T.operation_id;
- DELETE T
- FROM
- internal.event_messages AS T
- INNER JOIN
- #DELETE_CANDIDATES AS DC
- ON DC.operation_id = T.operation_id;
- DELETE T
- FROM
- internal.operation_messages AS T
- INNER JOIN
- #DELETE_CANDIDATES AS DC
- ON DC.operation_id = T.operation_id;
- -- etc
- -- Finally, remove the entry from operations
- DELETE T
- FROM
- internal.operations AS T
- INNER JOIN
- #DELETE_CANDIDATES AS DC
- ON DC.operation_id = T.operation_id;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement