Hi,
This is related to known issue of Octopus: Task Apply Retention policy is failing due to timeout issue.
http://help.octopusdeploy.com/discussions/problems/16322-apply-retention-policies-never-finishes
Below is from the log:
Note that this task only applies Octopus server-side retention policies for releases, events and package retention policies for the built-in package repository. Files on Tentacles will only be deleted when the retention policy is applied at the end of a deployment.
July 28th 2017 09:35:07Fatal
Exception occured while executing a reader for SELECT Deployment.* FROM dbo.VariableSet INNER JOIN dbo.Deployment ON VariableSet.OwnerId = Deployment.Id INNER JOIN dbo.ServerTask ON Deployment.TaskId = ServerTask.Id WHERE ServerTask.[State] IN ('Success', 'Failed', 'TimedOut', 'Canceled') AND Deployment.Id NOT IN (SELECT Id FROM MultiTenancyDashboard)
SQL Error -2 - Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The wait operation timed out
Also when i try to execute this from SQL Server, it is taking long time to complete, I had to cancel the query as there was no result in 10 min.On bifurcation I found that it finishes quickly if we remove the last part of the query that is (SELECT Id FROM MultiTenancyDashboard)