July 20, 2011 at 12:56 pm
Does any one knows any query to change audit trace log size?
July 20, 2011 at 2:07 pm
do you mean the default trace? for that, you can't, it's a black box...but you can script a copy of that same trace definition, modify THAT, and create a new trace with any changes you've made.
the same really goes for any other trace as well...you cna script it out, modify it, dropt he old, start the new, and your good to go.
I'm pretty proud of this script, which is out there as a contribution i think as well.
this lets you script any existing trace on your server so you can modify it:
--select * from sys.traces
--drop procedure sp_ScriptAnyTrace
--sp_ScriptAnyTrace 2
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 '
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 '
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
+ CONVERT(VARCHAR(8000),ISNULL(VALUE,' NULL'))
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
July 20, 2011 at 2:57 pm
OK great thanks for the script it diffidently great
However when you said the default is a so does that mean I will have 2 traces running or how does that work so if I m going to configure my own trace then what happened to the default trace when its enabled then running the script for the new trace with the parameters set is it there just as a template ?
July 20, 2011 at 3:01 pm
allamiro (7/20/2011)
OK great thanks for the script it diffidently greatHowever when you said the default is a so does that mean I will have 2 traces running or how does that work so if I m going to configure my own trace then what happened to the default trace when its enabled then running the script for the new trace with the parameters set is it there just as a template ?
If you do your own default trace you can disable the real default trace.
Keep in mind that some reports that come with SSMS will stop working without the default trace tho. But if you didn't already know about those then there's no real harm ;-).
July 20, 2011 at 3:06 pm
allamiro (7/20/2011)
OK great thanks for the script it diffidently greatHowever when you said the default is a so does that mean I will have 2 traces running or how does that work so if I m going to configure my own trace then what happened to the default trace when its enabled then running the script for the new trace with the parameters set is it there just as a template ?
i've got a link somewhere from grant fitchey i think, were he showed that if you had 10 different traces, and they all use the same event, the event is shared among all the traces and not logged 10 times, so there's no performance impact.
so you don't have to be concerned about a performance hit.
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply