RE: C2 Audit Trace Files Location SQL Server 2005

SSC Guru

Points: 323507

August 1, 2011 at 10:02 am

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

--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!