Using Audit Logs

SQL Profiler system stored procedures support file rollover. The maximum file size for the audit log is fixed at 200 megabytes (MB). When the audit log file reaches 200 MB, a new file will be created and the old file handle will be closed. If the directory fills up (for example, if the disk quota for the user of the service account has filled up or the disk is full), then the instance of Microsoft® SQL Server™ is stopped. The system administrator needs to either free up disk space for the audit log before restarting the instance of SQL Server or restart the instance of SQL Server (if auditing is not configured to start automatically).

Use file rollover to prevent the audit trace from failing because the audit log filled up. However, SQL Server will not shut down unless the user specifically requested this feature when they created the trace. An audit failure produces an entry in the Microsoft Windows® event log and the SQL Server error log.

It is strongly recommended that during SQL Server Setup you create a new directory to contain your audit files. \mssql\audit is the suggested path. If you are running SQL Server on a named instance, the suggested path is MSSQL$Instance\audit.