SSIS maintenance job applies data retention period incorrectly - by Dimitri Furman

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 805174 Comments
Status Resolved Workarounds
Type Bug Repros 0
Opened 10/11/2013 12:25:17 PM
Access Restriction Public


The "SSIS Server Maintenance Job" executes the internal.cleanup_server_retention_window stored procedure in the SSISDB database. The stored procedure retrieves the "Retention Period (days)" property of the SSIS Catalog, and then proceeds to delete data in the SSISDB database that is older than the number of days specified by that property.

This functionality is implemented incorrectly. Specifically, the comparison that restricts the data to be deleted involves the end_time column in the internal.operations table, which is declared as datetimeoffset(7), and a local variable named @temp_date, which is declared as datetime. Since a datetime value is treated as having time zone offset +00:00 when compared with a datetimeoffset value, and the end_time values are recorded in local time, this results in incorrect rows being deleted from tables in SSISDB, for any server that is in a time zone with non-zero offset.

For example, if the retention period is set to 1 day, and the server is located in a time zone with +12:00 offset, the maintenance job will delete any data that is older than 12 hours, causing unintended data loss.

The proposed fix is to change two lines in the stored procedure as follows, replacing commented lines with the lines that follow:

--DECLARE @temp_date datetime
DECLARE @temp_date datetimeoffset(7);

--SET @temp_date = GETDATE() - @retention_window_length
SET @temp_date = DATEADD(day, - @retention_window_length, SYSDATETIMEOFFSET());

Sign in to post a comment.
Posted by Microsoft on 10/17/2013 at 2:15 PM
Hello Dimitri.
Thank you for bringing this to our attention. We really do appreciate the feedback. We’ll investigate and get back to you.