Server version 2020.4.10 on MSSQL 2017
I saw a number of similar errors in the forum, but for different areas of the application and appeared to be resolved. I encountered this on the Infrastructure → Accounts → → View Usage. This report was working a couple weeks ago, but I think we just crossed the threshold. I trimmed out some of the parameters from the original error.
Octopus Server returned an error: Exception occurred while executing a reader for SELECT * FROM [dbo].[Release] WHERE ((([SpaceId] = 'Spaces-1'))) AND ([ProjectVariableSetSnapshotId] IN (@projectvariablesetsnapshotid1, @projectvariablesetsnapshotid2, @projectvariablesetsnapshotid3, **...** @projectvariablesetsnapshotid2116, @projectvariablesetsnapshotid2117)) ORDER BY [Id]
SQL Error 8003 - The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
Thanks for reaching out. Sorry to hear you’re having issues running this report.
It looks like the query is tripping up with the ProjectVariableSetSnapshotId column in the dbo.Release table, hitting the 2100 maximum parameter limit. You may verify how many records are in this table via the following SQL query:
SELECT * FROM [dbo].[Release]
Is the Octopus instance currently taking advantage of Retention Policies for Projects? If not, is this something that could be implemented? Retention Policies will remove old releases and subsequently reduce the number of records in the dbo.Release table.
For reference, here is our documentation on Retention Policies:
Let me know your thoughts at your earliest convenience.
Hi Garrett, Yes we do have retention policies enabled, we keep 3 releases for each lifecycle. We just have a lot of projects. Regardless shouldn’t this report use batches if there is a known hard query limit?
Thanks for that information. Unfortunately, we do not have an immediate workaround, so I’ve submitted a GitHub issue in order to get this corrected. You can follow it here:
If you have any more questions in the meantime please let us know.
Regardless shouldn’t this report use batches if there is a known hard query limit?