Q
Problem solve Get help with specific problems with your technologies, process and projects.

Getting a 'failure to write the audit trace' error

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.

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.
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:

  1. Stop the original trace.
  2. Zip the file and copy it to an archival location.
  3. 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.

This was last published in August 2005

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close