This article is contributed. See the original author and article here.

Today, I worked on a very interesting case that our customer is facing the following error message connecting to Azure SQL DB and/or Azure Managed Instance “Connection is not available, request timed out after ..”. 


 


Following, I would like to share with you my lessons learned about why and how this error message is fired in two different situations:


 



  • All the connections in the Hikari pooling are in use. 

  • The session limit for the database has been reached. 


 


In order to review these two things, I included the Hikari connection pooling based on this article: Improve Java application reliability with Azure SQL Database using JDBC and connection pooling. – Microsoft Tech Community,  and modifying the current Java code that you could find in this GitHub


 


My database is a standard 1 database that we have a session limit in 900. The first thing to probe my theory about the connection pooling is to configure setmaxiumpoolsize parameter to 50 and setCloseConnection to false to open this number of connections without closing any of them. In this situation, once I reached the connection number 51 I got the error message “Connection is not available”


 


 

    public static void main(String[] args) throws Exception{
        System.out.println("Testing connection JAVA! (Hikari)");
        ErrorClientHikari oErrorClient = new ErrorClientHikari();
                    oErrorClient.setCloseConnection(false);
                    oErrorClient.setReadingSQLData(false);
                    oErrorClient.setTotalIteractions(30000);
                    oErrorClient.setSQLReadToExecute("SELECT count(*) Id FROM PerformanceVarcharNVarchar where TextToSearch =N'Value'");
                    oErrorClient.setSQLCommandTimeout(30000);
                    oErrorClient.setServerName("servername.database.windows.net");
                    oErrorClient.setDatabaseName("dbname");
                    oErrorClient.setUserName("username");
                    oErrorClient.setPassword("password");
                    oErrorClient.setMaximumPoolSize(50);
                    oErrorClient.setConnectionTimeout(5000);
                    oErrorClient.LoadData();

 


 


In this similar situation, I’m going to modify the setmaxiumpoolsize parameter to 1000 to know what happen when I reached more connections that the session limit of my Azure SQL Database Standard 1. At this time, I got the same error message: “Connection is not available, request timed out after..” so, that means, that both situations will report the same error message.


 


 

    public static void main(String[] args) throws Exception{
        System.out.println("Testing connection JAVA! (Hikari)");
        ErrorClientHikari oErrorClient = new ErrorClientHikari();
                    oErrorClient.setCloseConnection(false);
                    oErrorClient.setReadingSQLData(false);
                    oErrorClient.setTotalIteractions(30000);
                    oErrorClient.setSQLReadToExecute("SELECT count(*) Id FROM PerformanceVarcharNVarchar where TextToSearch =N'Value'");
                    oErrorClient.setSQLCommandTimeout(30000);
                    oErrorClient.setServerName("servername.database.windows.net");
                    oErrorClient.setDatabaseName("dbname");
                    oErrorClient.setUserName("username");
                    oErrorClient.setPassword("password");
                    oErrorClient.setMaximumPoolSize(1000);
                    oErrorClient.setConnectionTimeout(5000);
                    oErrorClient.LoadData();
    }

 


 


Enjoy!


 


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.