0

Background:

SQL Compliance Manager is collecting files on an Agent Server to audit and once the trace files collect on the Agent the Compliance Manager agent service account moves these files to the Collection Server folder, processes them and deletes them.

Problem:

Over 5 times in the last month, the trace files have started filling up the Agent drive to the point where the trace files have to be stopped by running a SQL query to change the status of the traces. This has also had a knock on effect with the Collection Server and the folder on there starts to fill up excessively and the Collection Server Agent is unable to process the audit trace files. 4/5 times the issue occurred closely after a SQL fail over, however, the last time this trace error occurred there had been no fail over. The only thing that was noticeable in the event logs was that 3 SQL jobs went off around the time the traces started acting up.

Behaviour:

A pattern has been identified which shows on Windows Event Viewer that there is an execution timeout close or at the time the trace files start becoming unwieldy.

Error: An error occurred starting traces for instance XXXXXXXXX. Error: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.. The trace start timeout value can be modified on the Trace Options tab of the Agent Properties dialog in the SQLcompliance Management Console.

Although, I do not believe by just adjusting the Timeout settings will cause for the traces to stop acting in that way, as these are recommended settings and other audited servers have these same settings but do not act in the same way. The issue only persists with one box.

Questions:

I want to find out if anyone else has experienced a similar issue and if so, was the environment the issue happened in dealing with a heavy load? By reducing the load did it help or were there other remediation steps to take? Or does anyone know of a database auditing tool which is lightweight and doesn't create these issues?!

Any help or advice appreciated!

AK37
  • 33
  • 6
  • 1
    When you say trace files, do you mean from using profiler? If so, there's you're problem. Profiler, and traces, have been deprecated for awhile. If you switch to Extended Events you'll likely find this problem goes away. – Thom A Nov 29 '18 at 12:36
  • @Larnu yes they are Profiler traces that get generated. Do you know of any downside to using extended events in comparison to using trace files? – AK37 Nov 29 '18 at 14:02
  • 1
    Not really, no. This might be a good place to start: [Stairway to SQL Server Extended Events](http://www.sqlservercentral.com/stairway/134867/) – Thom A Nov 29 '18 at 14:05

0 Answers0