C2 Audit Trace Files Location SQL Server 2005
-
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.
-
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.
-
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
-
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 ?
-
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 -
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 -
no issues just want to wanted to have 50 MB
-
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! -
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.
-
I could not start the sql server after enabling this , why any help?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply