In the last few days, our SQL Server has been shut down several times due to a "failure to write the audit trace." Do we have the option not to have SQL Server shut down when the audit trace fails to write? We have the SQL Profiler running a trace against the subject SQL Server from another server.
QUESTION POSED ON: 25 AUG 2005
QUESTION ANSWERED BY: Kevin Kline
For questions like this, I recommend that you get familiar with the Microsoft SQL Server Support Center. You can get lots of great information about whether the behavior you're experiencing is a bug, has a hotfix, is resolved in a service pack, etc. I go to this source for SQL Server problems before Google.
Now, as for your exact problem, I suggest a multipart solution. You should set up the trace to have a maximum size, either its max size for the day or for the week. (You should have a pretty good idea how big the trace file will get.) Then set up a DTS job to do the following on either a daily or weekly schedule:
- Stop the original trace.
- Zip the file and copy it to an archival location.
- Start a new trace.
As far as I'm aware, this is the only way to ensure that you don't run out of space for the audit trace file.
Also, please remember that SQL Profiler is generally used as a diagnostic tool and not a long-term monitoring tool. I suggest you consider other monitoring options for the long term.
|
|
|
