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.

Question

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.

Vuyiswa Maseko 351

Good Day All

I have the following SP

CREATE PROCEDURE [dbo].[spx_Add_Update_USERS]                        
(                          

@USER_HANDLE nvarchar(max),                      
@TELEPHONE varchar(max),                         
@LANGUAGES nvarchar(max) = NULL,                     
@COUNTRY_CODE nvarchar(max) = NULL,                        
@AGE INT = NULL,                        
@GENDER nvarchar(max) = NULL,                         
@COUNTRY nvarchar(max),                          
@STATE_PROVICE nvarchar(max) = NULL,             
@MY_RACE nvarchar(max) = NULL,       
@HIS_RACE nvarchar(max) = NULL,                     
@LU_LOOKING_FOR  nvarchar(max) = NULL,                         
@BIO nvarchar(max) = NULL,       
@CITY_SURBURBS nvarchar(max) = NULL   ,
@LU_RELATIONSHIP_LOOKING_FOR nvarchar(max) = NULL ,
@TOKEN UniqueIdentifier

)                        
AS                      


declare @USER_ID INT  

DECLARE @USER_ID_TOK   INT  =(SELECT  TOP 1 [USER_ID] FROM [dbo].[USERS] WHERE  TOKEN=  @TOKEN    )

DECLARE @USER_ID_TEL   INT  =(SELECT TOP 1  [USER_ID] FROM [dbo].[USERS] WHERE [TELEPHONE] = @TELEPHONE) 



 IF(@USER_ID_TOK  IS NULL AND @USER_ID_TEL IS NOT NULL  )
 BEGIN 
 SET @USER_ID = @USER_ID_TEL
 END 
 ELSE 
 BEGIN 
 SET @USER_ID = @USER_ID_TOK
 END 


DECLARE  @LU_COUNTRIES_ID  INT  =(  SELECT  top 1  [Id] FROM [dbo].[Countries] WHERE [Name] =@COUNTRY )                    

DECLARE @LU_LANGUAGES_ID  INT =  (  SELECT   top 1  [LU_LANGUAGES_ID] FROM [dbo].[LU_LANGUAGES] WHERE [DESCRIPTION] =@LANGUAGES )                    

DECLARE @STATE_PROVICE_ID INT  =  (  SELECT  top 1  Id FROM [dbo].[Regions] WHERE [Name] =  @STATE_PROVICE )                    

--DECLARE @LU_LOOKING_FOR_ID  INT   =  (  SELECT  LU_LOOKING_FOR_ID FROM [dbo].[LU_LOOKING_FOR] WHERE [DESCRIPTION] =@LU_LOOKING_FOR )                     
--DECLARE @LU_RACE_ID  INT   =  (  SELECT  LU_RACE_ID FROM [dbo].[LU_RACE] WHERE [DESCRIPTION] =@RACE )              

DECLARE @CITY_SURBURB_ID INT  =  (  SELECT  top 1  Id FROM [dbo].[Cities] WHERE [Name] =@CITY_SURBURBS )                    


IF ( ( SELECT   COUNT(*) FROM     [dbo].[USERS] WHERE                          
USER_ID = @USER_ID  ) > 0 )                        

BEGIN UPDATE [dbo].[USERS]                        
SET [USER_HANDLE] =@USER_HANDLE ,[TELEPHONE] =@TELEPHONE                     
 , [LU_LANGUAGES_ID] =@LU_LANGUAGES_ID,                    
 [COUNTRY_CODE]= @COUNTRY_CODE,[GENDER] =@GENDER,                    
[LU_COUNTRIES_ID] =@LU_COUNTRIES_ID,[STATE_PROVICE_ID] =@STATE_PROVICE_ID,[LU_TOWNSHIP_ID] = @CITY_SURBURB_ID 
,MY_RACE =@MY_RACE,HIS_RACE=@HIS_RACE,
LU_RELATIONSHIP_LOOKING_FOR = @LU_RELATIONSHIP_LOOKING_FOR  
 ,IS_VERIFIED = 1 ,  AGE = @AGE  ,
  DATE_GENDER  = @LU_LOOKING_FOR , TOKEN = @TOKEN       
WHERE [USER_ID] =  @USER_ID                


END                        
ELSE                         
BEGIN                         

INSERT INTO [dbo].[USERS]([USER_HANDLE] , [LU_USER_STATUS_ID] ,                         
[TELEPHONE]      ,                         
[LU_LANGUAGES_ID]  , [COUNTRY_CODE] ,                        
AGE , [GENDER] , [LU_COUNTRIES_ID] , [STATE_PROVICE_ID] ,[LU_TOWNSHIP_ID] ,                         
MY_RACE,HIS_RACE , LU_RELATIONSHIP_LOOKING_FOR    ,IS_VERIFIED , DATE_GENDER,TOKEN)                        
VALUES(@USER_HANDLE , 1 --ACTIVE                    
, @TELEPHONE     , @LU_LANGUAGES_ID ,                         
  @COUNTRY_CODE , @AGE , @GENDER , @LU_COUNTRIES_ID ,                         
@STATE_PROVICE_ID,@CITY_SURBURB_ID , @MY_RACE,@HIS_RACE , @LU_RELATIONSHIP_LOOKING_FOR ,1  , @LU_LOOKING_FOR,@TOKEN)                        
END

and i use it like this

 public async Task<USERS_Model> Verify_code(USERS_Model model)
        {
            con = new SqlConnection(strCon);
            cmd = new SqlCommand();
            cmd.CommandText = "[dbo].[spx_Verify_code]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con; 
            cmd.CommandTimeout = 0;

            cmd.Parameters.Add("@PHONE_NUMBER", SqlDbType.NVarChar).Value = GenericMethods.RemoveSpacesIn_String(model.TELEPHONE);

            cmd.Parameters.Add("@VERIFICATION_CODE", SqlDbType.NVarChar).Value = GenericMethods.RemoveSpacesIn_String(model.VERIFICATION_CODE);


            if (model.COUNTRY != null)
                cmd.Parameters.Add("@COUNTRY", SqlDbType.NVarChar).Value = model.COUNTRY;

            if (model.USER_HANDLE != null)
                cmd.Parameters.Add("@USER_HANDLE", SqlDbType.NVarChar).Value = model.USER_HANDLE;


            if (model.COUNTRY_CODE != null)
                cmd.Parameters.Add("@COUNTRY_CODE", SqlDbType.NVarChar).Value = model.COUNTRY_CODE.Replace('+', ' ').Replace('+', ' ');

            if (model.AGE != 0)
                cmd.Parameters.Add("@AGE", SqlDbType.Int).Value = model.AGE;

            if (model.GENDER != null)
                cmd.Parameters.Add("@GENDER", SqlDbType.NVarChar).Value = model.GENDER;

            if (model.STATE_PROVICE != null)
                cmd.Parameters.Add("@STATE_PROVICE", SqlDbType.NVarChar).Value = model.STATE_PROVINCE;

            if (model.HIS_RACE != null)
                cmd.Parameters.Add("@HIS_RACE", SqlDbType.NVarChar).Value = model.HIS_RACE;

            if (model.MY_RACE != null)
                cmd.Parameters.Add("@MY_RACE", SqlDbType.NVarChar).Value = model.MY_RACE;

            if (model.LOOKING_FOR != null)
                cmd.Parameters.Add("@LOOKING_FOR", SqlDbType.NVarChar).Value = model.LOOKING_FOR;

                   if (model.CITY != null)
                cmd.Parameters.Add("@CITY_SURBURBS", SqlDbType.NVarChar).Value = model.CITY;


            if (model.RELATIONSHIP_LOOKING_FOR != null)
                cmd.Parameters.Add("@LU_RELATIONSHIP_LOOKING_FOR", SqlDbType.NVarChar).Value = model.RELATIONSHIP_LOOKING_FOR;

            if (model.TOKEN != null)
                cmd.Parameters.Add("@TOKEN", SqlDbType.UniqueIdentifier).Value = model.TOKEN;

            da = new SqlDataAdapter();

            da.SelectCommand = cmd;

            DataTable dt = new DataTable();

            string message = string.Empty;

            USERS_Model m = new USERS_Model();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["USER_ID"] != DBNull.Value)
                        m.USER_ID = Convert.ToInt32(dt.Rows[0]["USER_ID"]);

                    if (dt.Rows[0]["TOKEN"] != DBNull.Value)
                        m.TOKEN = Guid.Parse(dt.Rows[0]["TOKEN"].ToString());

                }

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m;
        }

for some reason im getting this error

Datalayer.Verify_code (Datalayer.USERS_Model model)
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.

Thanks

Viorel 122.9K Reputation points

2021-10-11T05:25:56.127+00:00

Do you have a spx_Verify_code procedure too?

Maybe con, cmd and da must be declared as locally. In addition, the null parameters probably must be added too.

Did you check if other database operations work well? Make sure that the connections are closed correctly.

3 answers

Your answer

Viorel 122.9K Reputation points

2021-10-11T05:25:56.127+00:00

Do you have a spx_Verify_code procedure too?

Maybe con, cmd and da must be declared as locally. In addition, the null parameters probably must be added too.

Did you check if other database operations work well? Make sure that the connections are closed correctly.

Answer 1

Dan Guzman 9,406

Use local variables and wrap the objects in a using block to ensure managed objects (SqlConnection and SqlCommand) are closed and disposed automatically. This also eliminates the need to add explict code to do so.

using(var con = new SqlConnection(strCon))
using(var cmd = new SqlCommand())
{
    //the rest of your method code here
}

Vuyiswa Maseko 351 Reputation points

2021-10-12T06:58:31.907+00:00

Thanks for the reply. but with the way i handled this on Finally , do you think it might be the issue ?
Dan Guzman 9,406 Reputation points

2021-10-12T10:13:47.007+00:00

No need for finally with the using block technique. The issue could be related to the scope of the connection and command objects, where those look to be declared outside the scope of the method instead of a local variable declared in the method.

In addition to improper connection management, a full connection pool may be a symptom of long-running queries. The root cause of long-running queries can be due to the need for query/index tuning, long-term blocking, or a SQL instance undersized for the workload, or a combination of these. Monitor SQL activity to see if long-running queries are the cause.

Not directly related to you problem is the proc code does not return a result set so the the data table will always be empty. It seems you want a SELECT query at the end of the proc to return the newly updated/inserted row.

Be aware the conditional insert/update proc code is vulnerable to race conditions when concurrent sessions operate on the same user. Use EXISTS instead of COUNT to check for existence of the user.

shuhaib k 11

As per C# 8 if we use using statement object will be released when out of scope, we don't need to use braces. Will this below code trouble us in the future if we don't close the connection and reader since we use async and await.
https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement

    public async Task<List<ResourceWrap>> getdata(string sqlQuery, string cString)  
    {  
        EnsureArg.IsNotEmptyOrWhiteSpace(sqlQuery, nameof(sqlQuery));  
        EnsureArg.IsNotEmptyOrWhiteSpace(resourceType, nameof(cString));  

        using SqlConnection conn = new SqlConnection(_connectionString.For(cString));  

        using SqlCommand cmd = new SqlCommand(sqlQuery, conn);  

        conn.Open();  
        using SqlDataReader reader = await cmd.ExecuteReaderAsync();  

        List<Resource> resources = await _fFactory.CreateResources(resourceType, reader);  

        return resources.Select(WrapResource).ToList();  
    }

Tom Phillips 17,771 Reputation points

2022-04-25T13:07:57.09+00:00

If you call
conn.Open();

You need to call
conn.Close();
shuhaib k 11 Reputation points

2022-04-27T05:57:12.753+00:00

I just wonder,why does it require to close the connection? here it says, if you use using object will be disposed of out of scope, that means the end of the method. Please advise

https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement
Jorge Alberto Moreno Gonzalez 6 Reputation points

2022-06-17T23:05:30.333+00:00
Actually you don't have to close it manually.

Se the following :https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection?view=dotnet-plat-ext-6.0

The first code example there has this

The following example creates a SqlCommand and a SqlConnection. The SqlConnection is opened and set as the Connection for the SqlCommand. The example then calls ExecuteNonQuery. To accomplish this, the ExecuteNonQuery is passed a connection string and a query string that is a Transact-SQL INSERT statement. The connection is closed automatically when the code exits the using block.

private static void CreateCommand(string queryString, string connectionString) { using (SqlConnection connection = new SqlConnection( connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); command.Connection.Open(); command.ExecuteNonQuery(); } }

Answer 2

AmeliaGu-MSFT 14,006 Microsoft External Staff

Hi VuyiswaiMaseko-8882,

It may be caused by not closing database connections correctly and consistently.
Please check if all connections are normally opened and closed.
And we can try to modify the connection string to increase "MaxPoolSize".
Please refer to this thread which might be helpful.

Best Regards,
Amelia

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Vuyiswa Maseko 351 Reputation points

2021-10-11T21:04:25.66+00:00

I am closing the connection in the Finally block.

Answer 3

This is not a SQL Server error, but a C# errors.

Please see:
https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#adding-connections

Note
Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. For more information, see Garbage Collection.

Share via

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.

3 answers

Your answer