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
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