The SSISDB's Catalog Properties included the data retention set to the default 365 days. There is only ONE Folder in this Catalog. Changing the retention to 7 days and running the "SSISDB Server Maintenance Job" causes the only package scheduled to run every 15 minutes, that typically runs in 90 seconds, to run for 19 minutes and fail with no explanation at all.
I can see the query below executing for over 2 hours (17GB of SSISDB Data). UPDATE STATISTICS is also running on a plethora of threads under the Maintenance Plan for the same DB:
DELETE TOP (@delete_batch_size)
OUTPUT DELETED.operation_id, DELETED.operation_type INTO @deleted_ops
WHERE ( [end_time] <= @temp_date
OR ([end_time] IS NULL AND [status] = 1 AND [created_time] <= @temp_date ))
I had hoped that the "Delete Batch Size" might be configurable, but no signs of it on the page dedicated to SSIDB at http://technet.microsoft.com/en-us/library/hh479588.aspx.
In running the User Package for over two months non-stop, there have been ZERO failures. The "SSISDB Server Maintenance Job" is clearly impeding normal operations. Given that this User process MUST run every 15 minutes, there is NO maintenance window in which the Maintenance Job could be scheduled so as to obviate this interfering behavior.
Please fix the delete process so it doesn't render the SSISDB inaccessible for NORMAL Production access.
(Stand-alone instance under VM-Ware)