Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

C2 Audit Trace Files Location SQL Server 2005 Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2007 6:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 11:08 AM
Points: 46, Visits: 105

Hi, becuase if and when the audit log file reaches its size limit of 200 megabytes (MB), SQL Server will create a new file, close the old file, and write all new audit records to the new file. This process will continue until the audit data directory fills up or auditing is turned off.  And when this happens SQL Server will stop responding.

My question is how do I change the location of the trace files?

THX in advance.

Post #396684
Posted Wednesday, September 5, 2007 7:01 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:19 PM
Points: 1,393, Visits: 438

I'm affraid the location cannot be changed, the files have to stay in the Data folder where your SQL installation is. What you can do is to implement an archiving procedure and move the archived logs.

Hopefully someone else with more C2 auditing experience will respond to this.

 




Post #396690
Posted Thursday, September 6, 2007 12:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 11:08 AM
Points: 46, Visits: 105

That was what I originally thought; however, I saw one of our servers had some trace files in the defualt location and then I noticed one trace file in a different location then the default I also noticed that the modified date was today so I know for sure that the location can indeed be changed.

HELP PLZ

 

Post #396742
Posted Wednesday, July 20, 2011 12:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 14, 2011 4:13 PM
Points: 16, Visits: 41
What if I want to change the size limits of the file to 50 MB for example is it also a default can not be changed ?
Post #1145388
Posted Friday, July 22, 2011 4:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
NBD4K27 (9/6/2007)


That was what I originally thought; however,I saw one of our servers had some trace files in the defualt location and thenInoticed one trace file in a different location then the default I also noticed that the modified date was today so I know for sure that the location can indeed be changed.

HELP PLZ <img src='images/emotions/sad.gif' height='20' width='20' border='0' title='Sad' align='absmiddle'>

You cannot change the location where the C2 audit trace files are written. The other trace files you're seeing must be from another trace running on your server.

References:

http://connect.microsoft.com/SQLServer/feedback/details/669702/c2-audit-files-location

http://technet.microsoft.com/en-us/library/ms187634.aspx


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1146966
Posted Friday, July 22, 2011 4:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
allamiro (7/20/2011)
What if I want to change the size limits of the file to 50 MB for example is it also a default can not be changed ?

I do not see anything that indicates that you can change the size of the trace file. What is your issue with the trace creating 200MB trace files?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1146972
Posted Monday, August 1, 2011 9:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 14, 2011 4:13 PM
Points: 16, Visits: 41
no issues just want to wanted to have 50 MB
Post #1151946
Posted Monday, August 1, 2011 10:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:11 PM
Points: 12,876, Visits: 31,789
the C2 trace and the default DDL trace are not editable...but...
a trace is just a trace.
you can script out the C2 trace, the default trace, or any other trace on your server, and then edit the resulting script, and create your own trace to do the same thing instead.

I have this handy procedure for Reverse Engineering a Server Side Trace that gives some nicely structured output of an existing trace that can help you out with that:

you just call sp_ScriptAnyTrace @traceID --1 being the traceId, which is C2 trace if active, else the the Default trace is 1
--select * from sys.traces
--drop procedure sp_ScriptAnyTrace
--sp_ScriptAnyTrace 3
ALTER PROCEDURE [dbo].[sp_ScriptAnyTrace](@traceid INT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Results TABLE(ResultsId INT IDENTITY(1,1),ResultsText VARCHAR(MAX))
--i thought about using a results table, decided i wanted a single varchar max string instead,
--then had to revert back to a results table because of concat truncation issues with implicit conversions to varchar(8000)

IF NOT EXISTS(SELECT 1 FROM sys.traces WHERE id = @traceid)
BEGIN
INSERT INTO @Results(ResultsText)
SELECT 'No trace exists with ID = ' + CONVERT(VARCHAR,@traceid)
SELECT * FROM @Results ORDER BY ResultsID
RETURN
END
INSERT INTO @Results(ResultsText)
SELECT '--declare variables for parameterizing the command ' UNION ALL
SELECT 'declare @traceidout int ' UNION ALL
SELECT 'declare @options int ' UNION ALL
SELECT 'declare @path nvarchar(256) ' UNION ALL
SELECT 'declare @maxfilesize bigint ' UNION ALL
SELECT 'declare @maxRolloverFiles int ' UNION ALL
SELECT 'declare @stoptime datetime ' UNION ALL
SELECT 'declare @on bit ' UNION ALL
SELECT ' ' UNION ALL
SELECT 'set @on = 1 --for scripting purposes, I think its better Always setting a script to start the trace after creation.'
--script the settings from sys.traces
INSERT INTO @Results(ResultsText)
SELECT 'set @maxfilesize = '
+ CASE
WHEN max_size IS NULL
THEN '20'
ELSE CONVERT(VARCHAR,max_size)
END + ' --size in MB '
FROM sys.traces WHERE id =@traceid

INSERT INTO @Results(ResultsText)
SELECT 'set @maxRolloverFiles = '
+ CASE
WHEN max_files IS NULL
THEN ' 5 '
ELSE CONVERT(VARCHAR,max_files)
END + ' --number of files; ie if 5 files, start rewriting on rollover '
FROM sys.traces WHERE id =@traceid

INSERT INTO @Results(ResultsText)
SELECT 'set @stoptime = '
+ CASE
WHEN stop_time IS NULL
THEN 'NULL'
ELSE '''' + CONVERT(VARCHAR(40),stop_time,121)+ ''''
END + ' -- null if never ends, else a specific date '
FROM sys.traces WHERE id =@traceid

INSERT INTO @Results(ResultsText)
SELECT 'set @options = '
+ CONVERT(VARCHAR,(2 * is_rollover) + (4 * is_shutdown) )
+ ' -- TRACE_FILE_ROLLOVER = '
+ CASE WHEN is_rollover = 1 THEN 'TRUE' ELSE 'FALSE' END
+ ', SHUTDOWN_ON_ERROR = '
+ CASE WHEN is_shutdown = 1 THEN 'TRUE' ELSE 'FALSE' END
FROM sys.traces WHERE id =@traceid

INSERT INTO @Results(ResultsText)
SELECT 'set @path = '''
+ CASE
WHEN PATH IS NULL
THEN 'mytrace'
ELSE LEFT(PATH,LEN(PATH) - 4)
END + '''' + ' -- the trace adds ".trc" to the pathname, so avoid "name.trc.trc" by removing it for scripting '
FROM sys.traces WHERE id = @traceid

INSERT INTO @Results(ResultsText) SELECT ''

--sp_trace_create [ @traceid = ] trace_id OUTPUT
--, [ @options = ] option_value
--, [ @tracefile = ] 'trace_file'
--[ , [ @maxfilesize = ] max_file_size ]
--[ , [ @stoptime = ] 'stop_time' ]
--[ , [ @filecount = ] 'max_rollover_files' ]
INSERT INTO @Results(ResultsText) SELECT ' --create the trace '
INSERT INTO @Results(ResultsText) SELECT ' exec sp_trace_create @traceidout output, @options, @path, @maxfilesize, @stoptime, @maxRolloverFiles '
--details
INSERT INTO @Results(ResultsText) SELECT ''
INSERT INTO @Results(ResultsText) SELECT ' --for the Event Every SQL statement completed, capture columns of accessible data '
--exec sp_trace_setevent @traceidout, 12, 6, @on --SQL:BatchCompleted,NTUserName
INSERT INTO @Results(ResultsText)
SELECT
' exec sp_trace_setevent @traceidout,'
+ CONVERT(VARCHAR(MAX),X.eventid) + ','
+ CONVERT(VARCHAR(MAX),X.columnid) + ',@on '
+ SPACE(74 - LEN( ' exec sp_trace_setevent @traceidout,'
+ CONVERT(VARCHAR(MAX),X.eventid) + ','
+ CONVERT(VARCHAR(MAX),X.columnid) + ',@on '
)
)
+ '--'
+ ISNULL(E.Name,'') + ','
+ ISNULL(V.name,'')
FROM ::fn_trace_geteventinfo(@traceid) AS X
INNER JOIN sys.trace_events E ON X.eventid = E.trace_event_id
INNER JOIN sys.trace_columns V ON X.columnid = V.trace_column_id

INSERT INTO @Results(ResultsText) SELECT '--filters'
INSERT INTO @Results(ResultsText) SELECT ''
INSERT INTO @Results(ResultsText) SELECT SPACE(75) + '-- WHERE 1 = 1'
INSERT INTO @Results(ResultsText)
SELECT
' exec sp_trace_setfilter @traceidout' + ','
+ CONVERT(VARCHAR,X.columnid) + ','
+ CONVERT(VARCHAR,logical_operator) + ','
+ CONVERT(VARCHAR,comparison_operator) + ','
+ CASE
WHEN VALUE IS NULL
THEN ' NULL '
--these are the(varchar) columns in the definition
WHEN X.columnid IN (6,7,8,10,11,26,34,35,36,37,38,39,40,42,45,46,47,59,64)
THEN ' N''' + CONVERT(VARCHAR(8000),ISNULL(VALUE,' NULL')) + ''' '
--these are the int/bigint columns in the definition
WHEN X.columnid IN (3,4,5,9,12,13,16,17,18,19,20,21,22,23,24,25,27,28,29,30,31,32,33,44,48,49,50,51,52,53,55,56,57,58,60,61,62,66)
THEN CONVERT(VARCHAR(8000),ISNULL(VALUE,' NULL')) + ' '
--image/ntest/datetime/uniqueidentifier columns (1,2,14,15,41,43,54,63,65)
ELSE ' N''' + CONVERT(VARCHAR(8000),ISNULL(VALUE,' NULL')) + ''' '
END
+ SPACE(72 - LEN(' exec sp_trace_setfilter @traceidout' + ','
+ CONVERT(VARCHAR,X.columnid) + ','
+ CONVERT(VARCHAR,logical_operator) + ','
+ CONVERT(VARCHAR,comparison_operator) + ','
+ CASE
WHEN VALUE IS NULL
THEN ' NULL '
--these are the(varchar) columns in the definition
WHEN X.columnid IN (6,7,8,10,11,26,34,35,36,37,38,39,40,42,45,46,47,59,64)
THEN ' N''' + CONVERT(VARCHAR(8000),ISNULL(VALUE,' NULL')) + ''' '
--these are the int/bigint columns in the definition
WHEN X.columnid IN (3,4,5,9,12,13,16,17,18,19,20,21,22,23,24,25,27,28,29,30,31,32,33,44,48,49,50,51,52,53,55,56,57,58,60,61,62,66)
THEN CONVERT(VARCHAR(8000),ISNULL(VALUE,' NULL')) + ' '
--image/ntest/datetime/uniqueidentifier columns (1,2,14,15,41,43,54,63,65)
ELSE ' N''' + CONVERT(VARCHAR(8000),ISNULL(VALUE,' NULL')) + ''' '
END) )
+ ' -- ' + CASE WHEN logical_operator = 0 THEN ' AND ' ELSE ' OR ' END + ISNULL(V.name,' NULL')
+ CASE
WHEN comparison_operator = 0 THEN ' = '
WHEN comparison_operator = 1 THEN ' <> '
WHEN comparison_operator = 2 THEN ' > '
WHEN comparison_operator = 3 THEN ' < '
WHEN comparison_operator = 4 THEN ' >= '
WHEN comparison_operator = 5 THEN ' <= '
WHEN comparison_operator = 6 THEN ' LIKE '
WHEN comparison_operator = 7 THEN ' NOT LIKE '
END
+ CASE
WHEN VALUE IS NULL
THEN ' NULL '
--these are the(varchar) columns in the definition
WHEN X.columnid IN (6,7,8,10,11,26,34,35,36,37,38,39,40,42,45,46,47,59,64)
THEN ' N''' + CONVERT(VARCHAR(8000),ISNULL(VALUE,' NULL')) + ''' '
--these are the int/bigint columns in the definition
WHEN X.columnid IN (3,4,5,9,12,13,16,17,18,19,20,21,22,23,24,25,27,28,29,30,31,32,33,44,48,49,50,51,52,53,55,56,57,58,60,61,62,66)
THEN ' ' + CONVERT(VARCHAR(8000),ISNULL(VALUE,' NULL')) + ' '
--image/ntest/datetime/uniqueidentifier columns (1,2,14,15,41,43,54,63,65)
ELSE ' N''' + CONVERT(VARCHAR(8000),ISNULL(VALUE,' NULL')) + ''' '
END
FROM ::fn_trace_getfilterinfo(@traceid) X
INNER JOIN sys.trace_columns V
ON X.columnid = V.trace_column_id

INSERT INTO @Results(ResultsText) SELECT '---final step'
INSERT INTO @Results(ResultsText) SELECT ''
INSERT INTO @Results(ResultsText) SELECT '--turn on the trace '
INSERT INTO @Results(ResultsText) SELECT ' exec sp_trace_setstatus @traceidout, 1 ---start trace '
INSERT INTO @Results(ResultsText) SELECT ' --exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it '
INSERT INTO @Results(ResultsText) SELECT ' --exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it '

SELECT * FROM @Results ORDER BY ResultsID
END





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1151959
Posted Tuesday, December 27, 2011 7:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2014 3:06 AM
Points: 6, Visits: 228
Controlling/customizing of C 2 to auditing is impossible and auditing solution similar to it is always very challenging task. I really appreciate your excellent work.
I was searching it from very long, in fact exactly the one I was needing.
thanks a lot on sharing this rare piece.
Post #1226887
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse