When monitoring a SQL Server Managed Instance, multiple opens with the "sleeping" status are opened. There should only be 2-3 sessions open per FglAM. This has the appearance of being a session leak.
An error similar to the following may appear in the database agent log file
java.lang.RuntimeException: Failed to execute collection [DBSS_Sessions], reason=[Foglight][SQLServer JDBC Driver]A problem occurred when attempting to contact the server (Server returned: Connection reset). Please ensure that the server parameters passed to the driver are correct and that the server is running. Also ensure that the maximum number of connections have not been exceeded for this server.
Caused by: com.quest.qsi.fason.framework.connections.common.exceptions.FrameworkConnectionException: [Foglight][SQLServer JDBC Driver]A problem occurred when attempting to contact the server (Server returned: Connection reset). Please ensure that the server parameters passed to the driver are correct and that the server is running. Also ensure that the maximum number of connections have not been exceeded for this server.- Profile:MSSQLProfile
Caused by: com.quest.qsi.fason.framework.connections.common.exceptions.FrameworkQueryException: [Foglight][SQLServer JDBC Driver]A problem occurred when attempting to contact the server (Server returned: Connection reset). Please ensure that the server parameters passed to the driver are correct and that the server is running. Also ensure that the maximum number of connections have not been exceeded for this server.
Caused by: java.sql.SQLNonTransientConnectionException: [Foglight][SQLServer JDBC Driver]A problem occurred when attempting to contact the server (Server returned: Connection reset). Please ensure that the server parameters passed to the driver are correct and that the server is running. Also ensure that the maximum number of connections have not been exceeded for this server.The Azure load balancer has a default timeout of four minutes for idle TCP connections. Idle connections that are waiting in our connection pool for more than four minutes will be disconnected by the load balancer.
The "Alter Any Connection" user permission is required to optimize the database connection for monitor Azure SQL Server Managed Instances and avoid potential session leaks between the SQL Server and SQL PI.
