SQL Server Agent service stopped because audit fails


I was stunned when I saw SQL Agent service is stopped unexpectedly on my SQL Server 2008 ENT. edition and there were no much informational message in SQL Server Error log (“SQLServerAgent initiating shutdown following MSSQLSERVER shutdown). But when I checked the Windows Error Log I got the below message…

“An error occurred while writing an audit trace. SQL Server is shutting down. Check and correct error conditions such as insufficient disk space, and then restart SQL Server. If the problem persists, disable auditing by starting the server at the command prompt with the “-f” switch, and using SP_CONFIGURE.”

For solutions, I surfed through MSDN, blogs, articles etc. and I got the below answers or the suggestion…

  1. Start perfmon and check for drive where Sql server writes the Log file check drive’s wait times. if your drive is too busy, it cannot keep up with writing the audit trace file, hence Sql server will have doubts the audit trace records all info, and so shut itself
  2. If you run any kind of server side trace with auto shutdown option, and for whatever reason trace cannot be written to file Sql server shuts down.

Configuration about my SQL Server environment…

  1. C2 Audit mode is not configured on the server
  2. Agent service is running under Service account.

Let’s have your comments…

References:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/eb94db75-fed1-4a99-b321-4a931de402ed

http://www.eggheadcafe.com/software/aspnet/33958815/sql2005-sp2-stopeed-becau.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s