Return
-
Title
Many SQL Trace ID started and stopped messages are in the SQL Server agent log files -
Description
Foglight floods the SQL Server log files with these messages:
"SQL Trace ID x was started by login 'Foglight Monitoring Account'"
and:
"SQL Trace stopped. Trace ID x. Login Name = 'Foglight Monitoring Account'"The SQL Trace is active for 15 minutes.
-
Cause
The sp_trace_getdata stored procedure is used by Foglight to collect SQL Server deadlock information. By default, if there are no activity, it closes / reopens the SQL Trace for deadlocks monitoring every 15 minutes (900 seconds). -
Resolution
The traces are needed to detect deadlock situations. The deadlock collection will reset (stop and start) the trace thread if one of the following options take place:- No event occurred in at least X minute time period (default 15 minutes)
- The trace ID does not appear in the SQL Server instance view 'sys.fn_trace_geteventinfo'
The attached script increase the idle time from restart every X minutes to once per day in order to reduce any affect that might be caused by the trace restarts. In the script you need to replace the variable agentName with the agent name (as seen under the Agent Status) you would like the change to be applied to.To apply the script:- Go to "Administration | Tooling | Script Console"
- In the lower part select on the tab "Scripts"
- Click on the green "Add" icon
- Copy the script into the popup window and click on "Run"
-
Attachments