Server log "SQL Trace" start and stop

Question

Server log "SQL Trace" start and stop

djj

SSCoach

Points: 18831
More actions
December 1, 2014 at 8:57 am

#312819

Hello, I did a search but I am not good at them, so I did not find an answer.
I am getting a log message in Window\Application stating "SQL Trace ID x was started by login "xxxx"." and "SQL Trace stopped. Trace ID = 'x'. Login Name = 'xxxx'." This is happening every six minutes. Note that x is an integer between 1 and 6 and xxxx is the sa account (not "sa").
How do I turn this off? I assume it is a flag, but I do not know the correct one.
Thank you,
djj

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

are you using any monitoring software? spotlight, etc?

there's a good chance that some monitoring software is creating a trace to gather stats every five minutes and then killing it's own trace.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

djj SSCoach Points: 18831 More actions · Answer 2

That could be. I am using a couple of Idera products (dm and cm). I will look into these.

Thanks!

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 3

Traces are logged within the default trace if that happens to be enabled on your instance. You can use the script below to pull out the application name of the trace start events:

/*

Reference: https://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/

*/

--File Growths

DECLARE @filename NVARCHAR(1000);

DECLARE @bc INT;

DECLARE @ec INT;

DECLARE @bfn VARCHAR(1000);

DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace

SELECT @filename = CAST(value AS NVARCHAR(1000))

FROM ::fn_trace_getinfo(DEFAULT)

WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces

SET @filename = REVERSE(@filename);

SET @bc = CHARINDEX('.',@filename);

SET @ec = CHARINDEX('_',@filename)+1;

SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));

SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number

SET @filename = @bfn + @efn

-- process all trace files

SELECT

ftg.NTUserName, ftg.ApplicationName, ftg.StartTime

FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg

INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id

WHERE ftg.EventClass = 117

AND ftg.EventSubClass = 1

ORDER BY ftg.StartTime

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

djj (12/1/2014)
That could be. I am using a couple of Idera products (dm and cm). I will look into these.
Thanks!

DM definitely has a trace that runs on a schedule. I think that the timing is configurable.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

djj SSCoach Points: 18831 More actions · Answer 5

The culprit is the compliance manager. According to Idera the only fix is to keep all error messages from the Windows Event Log. From Idera solutions:

Solution Number 00001798 Fixed In Version:
Created By Evan Rowlett, 2/8/2007 12:00 PM Last Modified By Frank Samstag, 4/27/2011 3:26 PM
Solution Title Many "Trace Started" and "Trace Stopped" Events from SQL Server are in the Windows Application Event Log of an audited SQL Server
Solution Details SOLUTION NUMBER: 00001798
SYMPTOMS:
On a SQL Server audited by SQL compliance manager, your windows Application event log shows many events for "SQL Trace Started" and "SQL Trace Stopped", with a source of the audited SQL instance.
CAUSE:
By default, SQL server error logging records an event in the Windows application log every time a trace is started or stopped.
RESOLUTION:
If you use the "-n" startup parameter, SQL Server error logging will not be performed to the event log. Be aware that this will also prevent the logging of other SQL server errors as events, including successful and failed backups. If you need to see this information, you can still find it in the SQL Server error log , located at "C:\Program Files\Microsoft SQL Server\MSSQL\LOG" by default.
For more information, please see the Microsoft Books Online article on Startup Parameters:
http://msdn2.microsoft.com/en-us/library/ms190737.aspx
Please note that these steps do not prevent trace events from being recorded directly to the SQL error log. It is not possible to prevent SQL Server from adding these particular events to the log.

[EDIT]

Found there is a trace flag 3688. It needs a restart of the services so I will not be able to implement it until the weekend. Since the culprit software only is licensed on one computer I cannot test on our dev system. :angry:

I will try and let you know what happens.