SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_trace_create option SHUTDOWN_ON_ERROR


sp_trace_create option SHUTDOWN_ON_ERROR

Author
Message
Jesse Reich
Jesse Reich
Say Hey Kid
Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)

Group: General Forum Members
Points: 678 Visits: 1024
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.
Jesse Reich
Jesse Reich
Say Hey Kid
Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)

Group: General Forum Members
Points: 678 Visits: 1024
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.
SQLBOT
SQLBOT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2054 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
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3870 Visits: 857
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search