SQL Server agent kills sessions (4373370)

CAUSE 1   (This is the most common cause)

Foglight killing sessions in the monitored host is actually a normal process. The database agent will periodically clean up its own idle sessions...

The DBSSSessionCleanerProcessor is the process that does this work, and should be listed with the session ID in the database agent log file like this

2025-03-23 14:50:09.095 ... - The idle DB agent sessions that will be killed are: [[215, 240]]

This DBSSSessionCleanerProcessor, identifies and kills "idle" database agent sessions in SQL Server instances. An "idle" session, is any session that was opened by the database agent but remained open on the server side, even after being closed on the client side (FglAM). 

The purpose of this processor is to clean up these orphaned sessions and prevent resource waste. If too many orphaned sessions remain open in a database, it can lead to several problems that can negatively impact the performance and stability of the SQL Server instance.

CAUSE 2

When a SQL Server host is monitored by two different Foglight agents, running SQL statements for the same collection may compete with one another causing processes to be deadlocked and killed.  Since the FMS is designed to avoid duplicate agents being created for the same host unless there are multiple hostnames assigned, this is more common associated with two FMS servers running concurrently monitoring the same host.

CAUSE 3

Users have the capability to manually kill sessions through the sessions dashboard.

RESOLUTION 1

WORKAROUND

Alternatively: attached to this knowledgebase article are two groovy scripts

• list_all_sql_agmKillOpenSessionOfClosedConnection_settings.groovy: list all of the SQL Server database agents that have the session cleaner capability enabled
• update_all_sql_agmKillOpenSessionOfClosedConnection_settings.groovy: enable or disable the sessions cleaner capability for all SQL Server database agents (use "0" to disable, and "1" to enable in line 8 of the script)

Users must be logged in to access the attached script

Quest R&D does not recommend turning off this process as it can result in an accumulation of idle processes on the host.

Potential issues of turning off this functionality can include

• Resource Exhaustion: Each open session consumes resources (like memory, CPU, and handles) on the server. When there are many orphaned sessions that are not actively being used but remain open, it can drain server resources, leaving fewer resources available for active tasks. If these sessions are not cleaned up, it can lead to resource contention, where the database system struggles to allocate sufficient resources for important operations, potentially slowing down the system or causing it to crash.
• Connection Pool Exhaustion: Many database systems (including SQL Server) use a connection pool to manage and reuse connections efficiently. If orphaned sessions occupy connections in the pool, new requests that need a connection may fail or be delayed, leading to poor performance or errors in applications that rely on the database. In extreme cases, the connection pool could become fully exhausted, preventing any new connections from being made, which could lead to service outages.
• Increased Latency: The database engine may need to handle a larger number of sessions, even though some of them are no longer needed. This can increase the time it takes for the database to process requests from active sessions, resulting in higher latency for operations like queries and transactions. Latency can severely degrade user experience, especially in real-time or high-performance applications.
• Deadlocks and Locking Issues: Orphaned sessions may hold database locks that prevent other transactions from accessing the necessary resources. This can lead to deadlocks, where two or more sessions are waiting on each other indefinitely, or locking conflicts, where other operations are delayed or blocked entirely. This can result in transaction failures and increased rollback overhead, impacting the integrity and performance of the system. 
• Database Performance Degradation: Having many idle or orphaned sessions on the server can cause the database to process unnecessary session management tasks. This overhead can slow down the system and reduce its ability to efficiently handle legitimate database operations. The system may also experience fragmentation in resources or internal buffers, leading to further inefficiencies and slower query execution.
• Increased Management Complexity: As the number of orphaned sessions increases, database administrators may have a harder time managing and monitoring the system. They may need to spend more time manually identifying and cleaning up these sessions, or they might miss critical performance problems caused by these idle sessions. If the orphaned sessions are not tracked or cleared, diagnostic efforts may become complicated, and root causes of performance issues may be harder to identify.
• Potential for Security Issues: In some cases, orphaned sessions may maintain certain privileges or connections to sensitive resources. These sessions, though inactive, could potentially be hijacked by malicious actors or remain open when no longer needed, posing security risks. For example, if a session is orphaned but still has open access to critical database resources, it might be exploited if the system is compromised.
• Licensing Implications: Some SQL Server licensing models are based on the number of active connections or sessions. If many orphaned sessions are left open, this could lead to unnecessary licensing costs if the system is being charged based on the number of sessions, users, or connections. 

STATUS

Enhancement Request FGSS-I-214 has been logged to add an visible agent status property value to turn off the session cleaner from the Agent Status properties page for the SQL Server agent. This will be reviewed by Product Management for consideration in a future release in Foglight.

RESOLUTION 2

Remove any sources of duplicate monitoring of the same host

RESOLUTION 3

Instead of granting sysadmin or processadmin roles, it's recommended to use the SQL Server agent grant script to prevent the agent from killing sessions from other users.

Beginning in the 7.3.0 release entries have been included in the Audit log when a session is manually killed using the Activity > Sessions dashboard.