How and Where is profiler trace file scheduled/coming from?

Question

Post reply

How and Where is profiler trace file scheduled/coming from?

debbie_jacob

SSC-Addicted

Points: 431
More actions
January 29, 2010 at 8:29 am

#144564

Hi,
There are over 1000 .trc files on our drive. The files are named audittrace20091211104655_1516.trc. It appears that this job was created/started on Dec 11, 2009. The files are all 204,800 KB. I am assuming that is the maximum file size and then it rolls over into the next file audittrace20091211104655_1517.trc, etc. I would like to stop this process.
Someone is running this trace on a development machine with almost 300 databases. There is certainly NO NEED for this to be running on every dev db for over a month.
I have opened the files looking for any indication where it might be coming from. It is not scheduled on this dev machine (not through SSMS or through Windows Scheduled Tasks). I have sent out an email asking who was running this trace. No one replied.
My question is -- is there anyway for me to determine from the files where this job is coming from? We have many development and user machines, too many that I don't even know all of them out there.
I need to kill this job. Thanks for your help!

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

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

starting with the obvious, did you select * from sys.traces? maybe it's a server side trace someone has created?

select * from sys.traces

--results

id status path max_size stop_time max_files

--- ------- ---------------------------------- ---------- ---------- ---------

1 1 c:\Pr...\MSSQL\LOG\log_143.trc 20 NULL 5

2 1 c:\Pr...\MSSQL\LOG\MyDMLtrace.trc 50 NULL 0

traceid 1 is the default trace, but anything above that was manually created.

you can stop any trace with this command, but i would recommend leaving traceid 1 alone; it's vvery low imact, a max of 5 small files, and can help you in the future.

I'd stop any additional traces, and find out who created it; that way, if it is valuable, you could always start it again.

closing the trace deletes it, so someone would have to rebuild/rescript it; that's why i'd just turn it off; i tmight save someone some dev time.

--exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it

--exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it

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!

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

You need to be careful. It sounds like C2 or Common Criteria Auditing is enabled. Run this to see:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'common criteria compliance enabled';

GO

sp_configure 'c2 audit mode'

GO

sp_configure 'show advanced options', 0;

GO

RECONFIGURE;

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

debbie_jacob SSC-Addicted Points: 431 More actions · Answer 3

Lowell,

Wow. Thanks. I had no idea this information existed. I ran the query: select * from sys.traces

idstatuspath

11\\?\E:\MSSQL\DATA\audittrace20091211104655_1581.trc

21C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_6507.trc

You mentioned that ID =1 is the default trace. In my case, it looks like the rogue trace is id =1. I am planning to stop the trace where id=1.

Thanks a LOT for your help.

I found this link on SSC that was useful as well:[/url]

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

In your case the default trace is NOT id 1. The default trace is id 2. The default trace will NEVER have more than 5 files, it is designed to roll over at 5 and you can't change that.

The "rogue" trace is C2 auditing. You need to find out WHY it is enabled and then determine IF it should be turned off.

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

debbie_jacob SSC-Addicted Points: 431 More actions · Answer 5

Jack,

You are right. C2 audit trace was enabled. I am pretty sure this was done accidentally since there is nothing worth monitoring on that machine.

I have disabled the option and have scheduled a time to restart sql server.

Thank you for replying! You solved my problem and I learned something new to boot.