April 8, 2009 at 7:15 am
I installed MS SQL on my server and as far as I can tell I chose all the defaults. However very large audittrace files (of the size of 204800 KB) are created every 7 minutes and are threatening to fill up my server. I did not intentionally create them and want to know how to stop them from being created. Does anyone know how to help out in this matter?
April 8, 2009 at 9:30 am
mldetch (4/8/2009)
I installed MS SQL on my server and as far as I can tell I chose all the defaults. However very large audittrace files (of the size of 204800 KB) are created every 7 minutes and are threatening to fill up my server. I did not intentionally create them and want to know how to stop them from being created. Does anyone know how to help out in this matter?
In which folder are they created? Are you talking about default trace files?
April 8, 2009 at 9:50 am
I would also like to verify what version of SQL Server you are running.
April 8, 2009 at 10:43 am
If you are running SQL Server 2000 what does this return:
SELECT * FROM fn_trace_getinfo(Default) AS FTG
If you are running 2005/2008 what does this return:
Select * From sys.traces
Also if you are running 2005/2008 then there should never be more than 5 of those files for the Default Trace.
Do you have C2 auditing enabled? You can check this by running:
sp_configure @configname = 'C2 audit mode'
You should have 0 in config_value and run_value.
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
April 8, 2009 at 12:35 pm
They are created in
c:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
April 8, 2009 at 12:36 pm
I am running MS SQL 2005 Standard version
April 8, 2009 at 12:45 pm
When I run "SELECT * from fn_trace_getinfo(Default) AS FTG"
I get the following:
traceid property Value
1 1 6
1 2 \\?\C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\audittrace20090407090827_200.trc
1 3 200
1 4 NULL
1 5 1
2 1 2
2 2 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_27.trc
2 3 20
2 4 NULL
2 5 1
April 8, 2009 at 12:46 pm
Also I turned the cw_audit mode off yesterday. Both values are zero. It did no good. I still get huge trc files
April 8, 2009 at 12:48 pm
Just checking, but did you also restart SQL Server?
April 8, 2009 at 1:22 pm
mldetch (4/8/2009)
W1 2 \\?\C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\audittrace20090407090827_200.trc
Could you tlell us what trace file is this one?
April 8, 2009 at 1:26 pm
Sorry, I'm new to this forum and I didn't read your question clearly enough. I am running SQL server 2005 and when I run
SELECT * from sys,traces
I get two lines with 18 columns
the 1st 3 column headers and info are as follows:
id status path
1 1 \\?\C:\Program Files\Microsoft SQL Server\MSSQL.1\MSQL\DATA\audittrace20090407090827_205.trc
2 1 c:\Program Files\Microsoft SQL Server\MSSQL.1\MSQL\LOG\log_27.trc
the next 6 columns and info are:
max_size stop_time max_files is_rowset is_rollover is_shutdown
200 NULL 0 0 1 1
20 NULL 5 0 1 0
the next 5 columns and info are:
is_default buffer_count buffer_size file_position reader_spid
0 4 128 14680064 NULL
1 2 128 393216 NULL
the last 4 columns and info are:
start_time last_event_time event_count dropped_event_count
2009-04... 2009-04..... 78080270 NULL
2009-04... 2009-04..... 1762 NULL
I hope this helps.
April 8, 2009 at 1:31 pm
I do not know how to answer you. My original question was that these trace files are showing up without me intentionally doing anything but installing MS SQL 2005 with all the defaults. They are just showing up every 7 seconds and I want to know three things:
1. What process creates them?
2. Why are they created?
3. How do I turn them off?
April 8, 2009 at 1:37 pm
Okay since, you are on SQL Server 2005, it looks like you may have common criteria compliance enabled (newer than C2 Auditing) or someone has configured a trace to start when SQL Server starts. To check if common criteria compliance enabled is on do this:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'common criteria compliance enabled'
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
I think it is common criteria because it is set to shutdown the SQL Server if it can't write to the file (is_shutdown = 1).
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
April 8, 2009 at 1:41 pm
Quickly, TraceID 2 is the default trace. You should never have more than 5 files and they don't get very big. the other trace, I suspect, is a C2or Common Criteria trace, as it looks like SQL Server would shut down if the trace could not write to disk.
You indicated that you shut down the C2 or Common Criteria option in an earlier post. Did you restart SQL Server after doing that?
April 8, 2009 at 1:42 pm
Jack is right, you could check that.
Or
Right click on your sever->Properties->Security and uncheck the 'Common Criteria Compliance'.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply