SSISDB getting large – failure of Retention Window Cleanup

The SSIS Catalog Rentention Period (days) defaults to 365.   The SSIS database can quickly become very large (as a function of SSIS activity and logging levels).

A stored procedure SSISDB.internal.cleanup_server_retention_window is executed by a job SSIS Server Maintenance Job on a nightly schedule at 00:00:00.

(All defaults observed in 2012, v.11.0.5058.0)

cleanup_server_retention_window

This stored procedure deletes rows from internal.operations table that are older than the retention period.  One row in internal.operations represents a package (?) invocation – this table won’t grow too large,  but related detail tables can;  internal.event_messages, internal.event_message_context, internal.operation_messages, internal.executable_statistics being the largest.

The cleanup stored procedure deletes rows from internal.operations tables.  Cascading foreign key constraints delete rows from other tables – some indirectly via internal.executions.  This is operational part of the procedure:

 WHILE (@rows_affected = @delete_batch_size)
   BEGIN
     DELETE TOP (@delete_batch_size)
       FROM [internal].[operations] 
       WHERE ( [end_time] <= @temp_date
       OR ([end_time] IS NULL AND [status] = 1 AND [created_time] <= @temp_date ))
     SET @rows_affected = @@ROWCOUNT
  END

Procedure continually fails to complete

The default for @delete_batch_size is 1,000 – and herein lies the problem.  The deletion of a single internal.operations row will cascade to many thousands of rows.  But internal.operations itself is relatively low cardinality.  Assembling such a large deletion set is a time consuming, log-expanding and blocking operation which can fail – and fail continually thereafter.  Although the deletion is sensibly in a loop, 1000 is a large proportion of  internal.operations  rows. As can be seen from the 16GB example above there are only 2.8K rows in the table to start with, so the loop would barely exceed a couple of iterations.    Naturally all this depends on the activity and retention period of the SSIS instance.  But 1,000 seems a lot in any case.

Some posts state that  @delete_batch_size is 10, though not in the versions I have checked.   If the procedure continually fails the solution is to run a copy the code with a smaller batch size to clear the backlog.   I am tempted to reduce the setting in the stored procedure itself – should a backlog of deletions accrue again for whatever reason the deletions will again fail.

The following chart shows the table sizes once the retention period has been reached – in this case, 2.8K rows in internal.operations were reduced to 827.  The projected results on the other large tables is significant.

Exporting data to Excel, but to named spreadsheet(s).  These notes are taken from an exercise using SSIS 2008R2.

  • Create the Data Flow and export data (in required format) to an Excel Destination (creating an Excel Connection Manager in the process).  Use this to extract the Sheet creation SQL.
    • In Excel Destination Editor select “Table or view” as the Data access mode
    • Click the New button and copy the “CREATE TABLE `Excel Destination` ( …”  code.    The backticks are legitimate.
  • Edit the Excel Connection Manager, set DelayValidation to False.
  • Create some string variables:
    • ExcelSheetName
    • SheetCreationStatement
  • In a script, generate the ExcelSheetName and assign the “CREATE TABLE…” code copied above to the SheetCreationStatement.   Replace “Excel Destination” (between the backticks) with your name for the worksheet.
  • Create an Execute SQL Task. Set ConnectionType = EXCEL, Connection = (your Excel Connection Manager), SQLSourceType = Variable, SourceVariable = User::SheetCreationStatement.     This task will create the worksheets.
  • Amend the Excel Destination
    • Set Data access mode to “Table name or view name variable”
    • Select User::ExcelSheetName for the Variable name.

Difficulties

  • Excel doesn’t have a large palette of types: most columns (in the CREATE TABLE statement) are defined as Long Text, Double or Long.  I found that all varchar columns had to be converted to unicode text stream (DT_NTEXT).
  • Set ValidateExternalMetadata to False for the Excel Destination. (the workbook will be blank to start with).
  • I got this Warning in the Create Worksheet Task – but despite this, the worksheet was created and the job ran satisfactorily
    • Warning: 0x0 at Create Worksheet SQL Task: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.  [warning appears twice per operation]
  • During testing you’ll see this Error if a worksheet with the name you’re trying to create already exists:
    • Error: 0xC002F210 at Create Worksheet SQL Task, Execute SQL Task: Executing the query “CREATE TABLE `2012-10-02`

Limitations

  • Unsupported characters in worksheet names are changed to _.  That’s OK, but also some supported characters – such as – (hyphen) are also changed to _.  A limitation of CREATE TABLE  statement?

References

Original pattern provided by these articles