Home Dashboard Directory Help
Search

SSIS maintenance job applies data retention period incorrectly by Dimitri Furman


Status: 

Resolved
 as Fixed Help for as Fixed


1
0
Sign in
to vote
Type: Bug
ID: 805174
Opened: 10/11/2013 12:25:17 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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());

Details
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.
Sign in to post a workaround.