I have a project that I had a trigger every 2 minutes for a while, and it started causing performance problems with my octo server after a couple of months. I’ve converted it to a runbook, and am trying to delete the old releases and the project. octo delete-releases fails on this:
Octopus Server returned an error: Error while executing SQL command in transaction ‘DeleteReleaseById.Delete|8000a5b8-0001-cb00-b63f-84710c7967bb|T449’: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Operation cancelled by user.
The statement has been terminated.
The command being executed was:
DELETE FROM [dbo].[Deployment] WITH (ROWLOCK) WHERE [Id] = @Id
SQL Error -2 - Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Operation cancelled by user.
The statement has been terminated.
The wait operation timed out.
Error from Octopus Server (HTTP 500 InternalServerError)
Exit code: -7
I’ve been able to clean up a couple of releases, but am having trouble with the ones that executed the most. Increasing the timeout doesn’t seem to help. Is there another query I could run directly on the sql server to help clean some of my mess up?
Thanks for getting in touch! I’m sorry to see you’re hitting this unfortunate issue. I can’t put my finger on any specific open or fixed bug report that I can confidently point you to, so I’d like to ask some clarifying questions if you don’t mind.
Which version of Octopus are you currently running?
How big is the range of releases that you’re trying to delete via the delete-releases command? Does decreasing the range in the command make any difference?
How many releases exist in total on this project?
Are you able to delete the releases that were deployed the most via the UI web portal, or is that method also showing this same error?
I look forward to hearing back and getting to the bottom of this one.
Hi Kenny,
We are running 2021.3 (8275) using the latest cli as well. I was able to delete the lesser deployed releases just fine. I am already targeting just one release to delete (–minVersion=0.0.3 --maxVersion=0.0.3). Attempting to delete via the UI does return the same error. If I had a way to increase the sql timeout on the connection, I’d bet I could get this done.
I’m just stepping in for Kenny while he’s offline. Thanks for answering those questions. I just had a few additional questions to ask to verify there are no underlying performance issues that may need to be addressed. These could very well all check out, but we just want to make sure we’re not missing something.
First and foremost, can you please confirm your system requirements on your instance are sufficient for the recent version of Octopus?
Are your SQL metrics okay? Are there plenty of available resources, Does the SQL Server host anything other than your Octopus database?
Can you please run a system integrity check to see if there are any issues? You can do this by clicking Configuration->Diagnostics->Run Check Now
The final check we can do is for SQL fragmentation. We have a step template that can do this if you do not have access to your SQL Server. It is called “SQL Server Fragmentation Query”.
My last question is to find how many deployments are associated with the specific release you are trying to delete. You should be able to find this via the API. If you need to find the ReleaseID you can open your browser’s dev tools when accessing the release page and you will see a response referencing the ID. Example: https://youroctopusurl/api/Spaces-1/releases/ReleaseId/deployments
I look forward to hearing back from you and please let me know if you have any further questions.
Hi Dan,
We are a bit underpowered on our SQL server, so I’m putting in a request for adding a couple more cores. Integrity check is good; I’ve run that a few times lately. I’ll implement the fragmentation query and see how it goes. The number of deployments in this case is extremely high, which I’m sure is the problem. As stated in the original description, that 2 minute trigger ran for a few months basically re-deploying the same release.
Not sure what changed, but I was able to delete one of the releases I was struggling with. The last release I need to delete still gets the timeout. The fragmentation report shows up to 75% fragmentation on mainly runbook indexes; 50% on a variety of things. Is there something I can run to reduce the fragmentation?
Here is another option that should help you get the release deleted. Even with more SQL resources you’re likely to continue hitting the timeout due to the number of deployments. This script will allow you to batch delete deployments from that release. Once enough deployments are deleted you should be able to delete the release.
##SETUP##
$OctopusUrl = "https://youroctopusurl"
$OctopusAPIKey = "API-123456789"
$ProjectName = "Project Name"
$ReleaseNumber = "0.0.3"
$amtOfDeploymentsToDelete = 1
##EXECUTION##
function Get($url){
$results = (Invoke-WebRequest $url -Verbose -Headers $header).content | Convertfrom-Json
return $results
}
function delete($url){
Invoke-WebRequest $url -Headers $header -Method Delete -Verbose
}
$header = @{ "X-Octopus-ApiKey" = $OctopusAPIKey }
$project = Get -url "$OctopusUrl/api/projects/all" | Where-Object {$_.Name -ieq $ProjectName}
$release = Get -url "$OctopusUrl/$($project.Links.Self)/releases/$ReleaseNumber"
$deploymentsInfo = get -url "$OctopusUrl/$($release.links.self)/deployments"
Write-Host "Project [$ProjectName] has [$($deploymentsInfo.totalresults)] deployments for release [$ReleaseNumber]"
$deploymentsToDelete = get -url "$OctopusUrl/$($release.links.self)/deployments?skip=$($deploymentsInfo.TotalResults - $amtOfDeploymentsToDelete)&take=$amtOfDeploymentsToDelete"
foreach($deployment in $deploymentsToDelete.items){
Write-Host "About to delete $($Deployment.id)"
delete -url "$OctopusUrl$($deployment.links.self)"
}
You just need to update the script with your Octopus instance URL, an API key with permission to delete deployments, the project name, and the release name. Let me know if this works for you or if you have any other questions.
Thanks for the script. Works great. I deleted 1k deployments and still have 23k left. Taking a second per, so will take me all day to trim these enough to delete the releases. I’ll respond after I get this mess cleaned up. Thanks for your help!