Every five minutes in each Deadlock collection period there are five connections opened for a trace.
It appears that a trace is opened for a very short duration and then other short traces are opened until one works for a long time.
The deadlock collection will reset (stop and start) the trace thread if one of the following options take place:
The SQL Server database agent uses the sp_trace_getdata to collect trace information from the monitored database.
WORKAROUND 1
The following Agent Status Properties (ASP) need to be updated to reduce the number sessions being constantly opened and not closing.
To apply the changes, the following steps need to be completed:
Note 1: The script "updateASP_allagents_231838.groovy" will change properties for all the DB_SQL_Server type agents, the script "updateASP_singleagent_231838.groovy" can be used to apply the change to a single agent.
---- BEGIN SCRIPT ----
configService = server.get("ConfigService");
agentService = server.get('AgentService')
---- END SCRIPT ----
Note 2: Once a fix for SSFOG-424 is released, the Agent Status Properties can be reverted to their original values (monReadSocketTimeout = 900 / deadLockMaxIdleTimeSec = 1800).
WORKAROUND 2
Disable the "Deadlock Data" collection.
WORKAROUND 3
STATUS
Beginning with the 5.9.4.20 and higher releases of the SQL Server cartridge the trace mechanism for deadlocks has been replaced with the use of Extended Events for SQL Server versions above 2005.
DISCLAIMER
Frequent closing and opening of traces can cause message like "Return closed connection JDBCConnection" to appear on the Foglight Agent Manager logs. These messages are to inform that the SQL trace connection (opened by the Foglight SQL Server agent monitoring an specific SQL Server instance) has been closed for some reason; either by Foglight or other conditions in the environment, and the Foglight for SQL Server agent tried to return the connection pool; as the connection is already closed, the message is logged.
The WORKAROUND 1 provided above, can help reduce the numbers of connections being opened constantly by Foglight, but there could be other reasons for the connections to be closed (not by Foglight for SQL Server agent):
Additional KB Articles:
Many SQL Trace ID started and stopped messages are in the SQL Server agent log files (229591)
Impact of disabling Foglight SQL Server traces (92488)
How to stop the SQL Server trace? (90089)
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center