SQL Server agent kills sessions (4373370)

CAUSE 1

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.

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

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.

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

RESOLUTION 2

Remove any sources of duplicate monitoring of the same host

RESOLUTION 3

FOG-7726 was created to add an entry in the Audit log when MANUALLY killing a session from the Activity > Sessions dashboard.