SQL Server 2008 : Audit-Related Startup and Shutdown Problems

2/27/2011 10:53:04 AM
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.

Figure 1. Error message received when attempting to start SQL Server but unable to initialize the target file

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.

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone