August 5, 2011 at 7:42 am
Hello all; I'm a SQL newbie and first-time poster. I stumbled upon this site and thought I'd ask a question.
I am working to get a SQL installation "compliant" with our required configuration. One of these is the following:
"SQL Server shall be configured to halt if a failure in audit file rollover occurs."
Apparently, since this was indicated as a failure, it's not currently set that way. The failure is detailed as "The following audit traces are not configured to halt SQL Server if a failure in audit file rollover occurs: 2.."
I've googled around and poked around SSMS but can't find a solution to this. Can anyone help? I would be most appreciative.
August 5, 2011 at 12:38 pm
it has to do with one of the parameters in the trace creation:
http://msdn.microsoft.com/en-us/library/ms190362.aspx
if you have a trace like this, where @options = 2:
you need @options = 6 to enable stop on error::
--declare variables for parameterizing the command
2 declare @traceidout int
3 declare @options int
4 declare @path nvarchar(256)
5 declare @maxfilesize bigint
6 declare @maxRolloverFiles int
7 declare @stoptime datetime
8 declare @on bit
9
10 set @on = 1 --for scripting purposes, I think its better Always setting a script to start the trace after creation.
11 set @maxfilesize = 20 --size in MB
12 set @maxRolloverFiles = 5 --number of files; ie if 5 files, start rewriting on rollover
13 set @stoptime = NULL -- null if never ends, else a specific date
14 set @options = 2 -- TRACE_FILE_ROLLOVER = TRUE, SHUTDOWN_ON_ERROR = FALSE
15 set @path = 'D:\SQLServer\MSSQL.1\MSSQL\LOG\log_47' -- the trace adds ".trc" to the pathname, so avoid "name.trc.trc" by removing it for scripting
7 --create the trace
18 exec sp_trace_create @traceidout output, @options, @path, @maxfilesize, @stoptime, @maxRolloverFiles
Lowell
August 5, 2011 at 12:46 pm
Thanks so much for the reply.
According to the MS article you provided the link to, "sp_trace_create only creates a trace definition. This stored procedure cannot be used to start or change a trace."
As far as I know, we just enabled C2 auditing straight out of the box. We're not using any custom trace audit.
Is there a way to change the default behavior or will I need to create a custom trace audit?
I apologize in advance if that's a horribly stupid question. I'm just trying to get this thing resolved so I can move on to the other hundred things that are piled up for me. :^)
August 5, 2011 at 12:59 pm
AFAIK, the C2 trace should already have the STOP on error flag in place,
at least when i read this:
http://technet.microsoft.com/en-us/library/cc293615.aspx
it's a requirement of that type of audt.
double check with the SQL belwo....if you enabled C2 (and also stoped and started the service!) it should be stop on error.
SELECT * from sys.traces
can you run this command and see if the case statement says SHUTDOWN_ON_ERROR =TRUE?
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 = 1 --traceid = 1(one) is the C2 Audit IF it is enabled, the default trace if it is not.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply