Hi ProGet Support,
We have an issue where our High-Availability ProGet infrastructure uses over 1000 SQL connections and reaches the max pool size. Some investigation on the SQL server using the activity monitor has shown the following queries running.
CREATE PROCEDURE [PgvdVulnerabilities_GetVulnerabilitiesForPackage]
(
@PackageName_Id INT
)
AS
BEGIN
SELECT * INTO #PgvdPackageNames FROM [PgvdPackageNames_Extended] WHERE [PackageName_Id] = @PackageName_Id
SELECT * FROM [PgvdVulnerabilities_Extended]
WHERE [Pgvd_Id] IN (SELECT [Pgvd_Id] FROM #PgvdPackageNames)
SELECT * FROM #PgvdPackageNames
SELECT * FROM [PgvdAssessments_Extended]
WHERE [Pgvd_Id] IN (SELECT [Pgvd_Id] FROM #PgvdPackageNames)
END
CREATE PROCEDURE [Feeds_GetExtendedConfiguration]
(
@Feed_Id INT
)
AS
BEGIN
SET NOCOUNT ON
SELECT *
FROM [FeedPackageAccessRules]
WHERE [Feed_Id] = @Feed_Id
ORDER BY [Sequence_Number]
SELECT C.*
FROM [FeedConnectors] FC
INNER JOIN [Connectors] C
ON C.[Connector_Id] = FC.[Connector_Id]
WHERE FC.[Feed_Id] = @Feed_Id
ORDER BY FC.[Sequence_Number]
SELECT *
FROM [FeedPackageFilters]
WHERE [Feed_Id] = @Feed_Id
ORDER BY [Sequence_Number]
END
This is causing an object lock
Our CD/CI process keeps requesting packages, and within 5 minutes, all the have 1000's of connections, and we get the following errors in ProGet when the lock releases.
An error occurred processing a GET request to http://proget.XXXXX/npm/Registry/cache-base: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
Details:System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
at Microsoft.Data.Common.ADP.ExceptionWithStackTrace(Exception e)
--- End of stack trace from previous location ---
at Inedo.Data.SqlServerDatabaseContext.CreateConnectionAsync()
at Inedo.Data.DatabaseContext.ExecuteInternalAsync(String storedProcName, GenericDbParameter[] parameters)
at Inedo.Data.SqlServerDatabaseContext.ExecuteInternalAsync(String storedProcName, GenericDbParameter[] parameters)
at Inedo.Data.DatabaseContext.ExecuteTableAsync[TRow](String storedProcName, GenericDbParameter[] parameters)
at Inedo.ProGet.WebApplication.FeedEndpoints.Npm.NpmPackageMetadataHandler.TryProcessRequestAsync(AhHttpContext context, WebApiContext apiContext, NpmFeed feed, String relativeUrl)
at Inedo.ProGet.WebApplication.FeedEndpoints.Npm.NpmHandler.ProcessRequestAsync(AhHttpContext context, WebApiContext apiContext, NpmFeed feed, String relativeUrl)
at Inedo.ProGet.WebApplication.FeedEndpoints.FeedEndpointHandler.FeedRequestHandler.ProcessRequestAsync(AhHttpContext context)
We are running version 23.0.31.
Regards Scott
