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

sp_trace_create option SHUTDOWN_ON_ERROR Expand / Collapse
Author
Message
Posted Tuesday, August 11, 2009 4:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:18 PM
Points: 164, Visits: 962
BOL states the following about the option SHUTDOWN_ON_ERROR: "Specifies that if the trace cannot be written to the file for whatever reason, SQL Server shuts down. This option is useful when performing security audit traces.

When running a server-side trace, is there any way that the trace file could not be written to with the exception of:

1) disk drive where trace file resides runs out of space
2) max_file_size of trace file is exceeded and there is no rollover option

While the trace is running, I have tried deleting the file, changing the name, changing the file permissions, and changing permissions and name on the folder in which the file resides. None of these stop SQL Server from writing to the file.

I'm also wondering if anyone knows how shutting down SQL Server if the file cannot be written to is *useful* in performing audit traces.

I ask these questions because my company's security regulation requires that if we do not use C2 auditing, a server-side trace must utilize the SHUTDOWN_ON_ERROR option. But I'm nervous about the idea of SQL Servers shutdowns happening outside of our control.
Post #769012
Posted Friday, August 14, 2009 10:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:18 PM
Points: 164, Visits: 962
Considering nobody has responded, I'm wondering if the question was phrased poorly, placed in the wrong forum, or simply not interesting. But for what it's worth, I did find a way to cause a SQL Server shutdown on a trace with the shutdown_on_error property.

First I created a server-side trace file (call it mytrace.trc) with option 6 (rollover 2 + shutdown_on_error 4). I set the maxfile size = 1 MB. Then I added the SP:statement event to the trace and started it. Afterwards, I created a text file in the same directory called mytrace_1.trc. Finally I began running sql queries until the trace file filled up to 1MB. Once it hit that point, it tried to "rollover" and create the next file in the trace sequence (mytrace_1.trc) but couldn't since it already existed. At that point, SQL Server stopped. I don't think this was something that would happen in real life but still it was kind of interesting.

After working with server-side traces and C2 auditing, I think the shutdown_on_error property exists simply so you can create audit traces that behave in the same way as C2 auditing but without all the overhead.
Post #771095
Posted Monday, August 17, 2009 10:17 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, Visits: 836
You're probably right about logging to a file. not much can go wrong there.

I suppose if you log to a table instead of a file anything that would prevent that would cause the server to shutdown.


~BOT


Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Post #772070
Posted Saturday, June 7, 2014 12:17 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:10 PM
Points: 2,669, Visits: 781
This is an old post. I agree about the nervousness. I am wondering what other conditions might cause the auditing to shutdown the server? If I change any part of the audit when it is in Shutdown mode, (and this is in production), I have to assume the server is going to shut down.

Jamie
Post #1578593
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse