Before setting up SQL Server audits, you should be
aware that audit problems can lead to trouble in starting or stopping
your SQL Server instance.
1. Failure to Start
When either the ON_Failure = On
or Shutdown Server on Audit Log Failure option is checked, SQL Server
will not start if it cannot initialize the target for an audit or if it
cannot write to the target of an audit. When SQL Server starts, it will
attempt to start any enabled audits. If failures occur, you will receive
an unfriendly error message like the one in Figure 1.
An audit target may not
initialize for a number of reasons. The drive might not allow you to
allocate all the space required when Reserve_Disk_Space
is set to on, the problem might be from changes in the file structure
where the audit files are stored, and so forth. Whatever the problem,
the result is that SQL Server does not start.
If you are like us, then
you probably don't want your server not starting because of an audit not
initializing. If that happens, then start SQL Server from the command
line using the following:
sqlservr.exe -f -s < instance name>
The instance name is optional, and defaults to MSSQLServer. The -f option will start SQL Server in minimal configuration mode, which allows you to disable or fix the troublesome audit target.
2. Forced Shutdowns
If SQL Server shuts down because of a failure to write to the log, then it will write an event to the error log stating MSG_AUDIT_FORCED_SHUTDOWN.
When this occurs, SQL Server still will not start until the problem
that caused the shutdown is resolved. To bypass this issue, you can
start SQL Server from the command line using the following:
sqlservr.exe -m -s<instance name>
As before, the instance name is optional. Using -m starts SQL Server in single user mode and allows you to disable the audit or change the shutdown option in order to continue.
You may be thinking that that this situation can easily be avoided by setting your audit to CONTINUE instead of SHUTDOWN.
On the other hand, you may have a very good reason for shutting down
SQL Server when you cannot log audit information. In either situation,
rather intentionally or unintentionally, you may find yourself unable to
start SQL Server after an audit shutdown, and you need to know how to
get your server back up and running.
Useful SQL Server Audit Objects
We want to make sure you know how to quickly determine the
active audits within your SQL Server instance. We also want you to know
how to read data from an audit file without using the GUI. The following
list describes the sys.dm_server_audit_status DMV and the fn_get_audit_file function.
sys.dm_server_audit_status:
Lists information for all the SQL Server audits. The DMV contains the
current status of the audits, the UTC time of the status change, and for
file audits, the location and size of the files.
fn_get_audit_file:
Enables you to read the file of an audit. The function expects three
parameters: the path and file name (which can include a wildcard), the
initial file name to start reading from, and the audit file offset
(which tells the function where to start reading from). You can specify default or null
for the last two parameters. The output of the function is a list of
audit records ordered by the time that the actions occurred. Having the
ability to review audit files and determine which audits are running
proves useful in our environments. We hope these two commands are useful
to you as well.
