Enabling Retention Policy when a lot of versions need deletions blocks all tasks

execution

#1

After enabling Retention Policy for the first time after a few years of usage octopus had to delete a lot of versions (6000+). Once the policy was in place Octopus stopped executing any task (deployment, …).

The error in the server log is :

An unexpected error occurred while attempting to retrieve and execute a task: Exception occured while executing a reader for `SELECT * FROM dbo.[ServerTask] with (UPDLOCK,READPAST) WHERE ([State] = 'Queued' AND [HasPendingInterruptions] = 0 AND ([ServerNodeId] IS NULL OR [ServerNodeId] = @servernodeid) AND [Id] IN (@potentialtasks_0, @potentialtasks_1, @potentialtasks_2, @potentialtasks_3, @potentialtasks_4, @potentialtasks_5, @potentialtasks_6, @potentialtasks_7, @potentialtasks_8, @potentialtasks_9, @potentialtasks_10, @potentialtasks_11, @potentialtasks_12, @potentialtasks_13, @potentialtasks_14, @potentialtasks_15, @potentialtasks_16, @potentialtasks_17, @potentialtasks_18, @potentialtasks_19, @potentialtasks_20, @potentialtasks_21, @potentialtasks_22, @potentialtasks_23, @potentialtasks_24, @potentialtasks_25, @potentialtasks_26, @potentialtasks_27, @potentialtasks_28, @potentialtasks_29, @potentialtasks_30, @potentialtasks_31, @potentialtasks_32, @potentialtasks_33, @potentialtasks_34, @potentialtasks_35,

...

@potentialtasks_6721, @potentialtasks_6722) AND [QueueTime] <= @queuetime  AND ([ConcurrencyTag] IS NULL OR [ConcurrencyTag] NOT IN (@concurrencytags_0)) ) ORDER BY [QueueTime], [Id]` 

The reason seem to be that the request built goes over the 2100 parameters limit of SQL Server.

We’ve edited the db to cancel most of the task and will manually batch them so don’t really need assistance but it’s still a bug :slight_smile:

PS: Deletions are also very slow, not because they run slowly but because there seem to be quite a time before a task is executed and the next one

Octopus: 2018.2.8
DB Server: SQL Server


(Nathan Skerl) #2

This was fixed in 2018.2.9 (I encountered the same issue as you and reported)


#4

Hi @vbfox_itg,

Just wanted to confirm that @nskerl is on the money, if you can update to at least 2018.2.9 that will resolve the issue for you.

Sorry that you ran into this one,

Regards,
Alex


#5

Oh thanks so we missed the fix by one minor version :laughing: I’ll run the update.

Thanks again.


#6

HI @vbfox_itg

If you haven’t done the upgrade yet we made this even better in 2018.5.1, as we enabled batch deletes which reduces the thousands of tasks problem.

Sorry that I didn’t point this out earlier,

Regards,
Alex


(system) #7

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.