Many very large audittrace files created on my server

Question

Many very large audittrace files created on my server

Viewing 15 posts - 16 through 30 (of 30 total)

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

mldetch Old Hand Points: 307 More actions · Answer 1

when I run the sp_configure commands I receive an error message stating the 'common criteria compliance enabled' does not exist or it may be an advanced option.'

I have just restarted my SQL server.

I now have over 30 audittrace files of over 204 MG in size. they are created approximately every 7 minutes.

-

Perry Whittle SSC Guru Points: 234065 More actions · Answer 2

from this

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

traceid 1 is your culprit and this "1 5 1" means its still running. Try stopping and closing the trace using

exec sp_trace_setstatus 1, 0 --stop trace

exec sp_trace_setstatus 1, 2 --close trace

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

"Ya can't make an omelette without breaking just a few eggs" 😉

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

Did you run ALL the sp_configure commands in the same order I listed them? The first one enables you to see the advanced options which is what common criteria is.

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

Perry Whittle SSC Guru Points: 234065 More actions · Answer 4

this is not a default trace as that logs to the LOG directory not the data directory

stop and close the trace as i specified, then go hunt down how this traces starts up (could be a scheduled job or something that creates the definition and/or starts the trace

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

"Ya can't make an omelette without breaking just a few eggs" 😉

mldetch Old Hand Points: 307 More actions · Answer 5

when I try the exec commands it tells me that I have to use sp-configure to turn it off.

mldetch Old Hand Points: 307 More actions · Answer 6

I entered them just as you gave them to me and ran them.

Perry Whittle SSC Guru Points: 234065 More actions · Answer 7

so it sounds like it is common criteria, did you run the code Jack posted above?

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

"Ya can't make an omelette without breaking just a few eggs" 😉

maechismo_8514 SSCoach Points: 17510 More actions · Answer 8

Perry Whittle (4/8/2009)
from this
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
traceid 1 is your culprit and this "1 5 1" means its still running. Try stopping and closing the trace using
exec sp_trace_setstatus 1, 0 --stop trace
exec sp_trace_setstatus 1, 2 --close trace

Perry that is a default trace isn't it? We cannot stop it unless we disable the default trace? Or am i mistaken?

mldetch Old Hand Points: 307 More actions · Answer 9

the very large files are audittrace2009.......trc files

and they are in the data directory.

Perry Whittle SSC Guru Points: 234065 More actions · Answer 10

no as Lynn pointed out id 2 is the default trace

what version of sql server is this as common criteria is only available in dev or Ent sql editions

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Perry Whittle SSC Guru Points: 234065 More actions · Answer 11

mldetch (4/8/2009)
I receive an error message stating the 'common criteria compliance enabled' does not exist or it may be an advanced option.'

it doesnt exist as you are not running sql ent or dev edition

with the server restarted re run this

sp_configure 'show advanced options', 1 ;

GO

RECONFIGURE ;

GO

sp_configure 'c2 audit mode';

GO

RECONFIGURE ;

GO

C2 audit creates trace files of 200MB and will roll over if the file fills up, see the MS tech notes here

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

"Ya can't make an omelette without breaking just a few eggs" 😉

mldetch Old Hand Points: 307 More actions · Answer 12

Okay, guys the creation of the 200 mb audittrace files have stopped. I guess it was the last sp_configure commands you gave me. I appreciate your help.

Now my question is why are these files so big and why are they created so often and what is their purpose?

I noticed that when I click on the properties of my sql server and go to security there is a box for 'Enable C2 audit tracing' and one for 'Cross database ownershsip chaining'. Both are unchecked at this time.

Perry Whittle SSC Guru Points: 234065 More actions · Answer 13

this details it

http://msdn.microsoft.com/en-us/library/aa196700.aspx

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

"Ya can't make an omelette without breaking just a few eggs" 😉

mldetch Old Hand Points: 307 More actions · Answer 14

Great. I appreciate all yalls help. I'm a newby Db admin and this is slightly challenging. Again, thanks.

Perry Whittle SSC Guru Points: 234065 More actions · Answer 15

you're not alone, i was sure the c2 audit wrote trace files to the LOG folder as well as the default trace, shows how wrong you can be. Then, i dont usually enable it unless really necessary because as you found out it logs literally everything, hence the file sizes. You got sorted that's the main thing

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

"Ya can't make an omelette without breaking just a few eggs" 😉