CREATE SERVER AUDIT (Transact-SQL)
Creates a server audit object using SQL Server Audit. For more information, see Understanding SQL Server Audit.
CREATE SERVER AUDIT audit_name
{
TO { [ FILE (<file_options> [ , ...n ]) ] | APPLICATION_LOG | SECURITY_LOG }
[ WITH ( <audit_options> [ , ...n ] ) ]
}
[ ; ]
<file_options>::=
{
FILEPATH = 'os_file_path'
[ , MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]
[ , MAX_ROLLOVER_FILES = { integer | UNLIMITED } ]
[ , RESERVE_DISK_SPACE = { ON | OFF } ]
}
<audit_options>::=
{
[ QUEUE_DELAY = integer ]
[ , ON_FAILURE = { CONTINUE | SHUTDOWN } ]
[ , AUDIT_GUID = uniqueidentifier ]
}
- TO { FILE | APPLICATION_LOG | SECURITY_LOG }
Determines the location of the audit target. The options are a binary file, The Windows Application log, or the Windows Security log. SQL Server cannot write to the Windows Security log without configuring additional settings in Windows. For more information, see How to: Write Server Audit Events to the Security Log.
NoteWriting to the Security log is not available on Windows XP.
- FILEPATH ='os_file_path'
The path of the audit log. The file name is generated based on the audit name and audit GUID.
- MAXSIZE = { max_size }
Specifies the maximum size to which the audit file can grow. The max_size value must be an integer followed by MB, GB, TB, or UNLIMITED. The minimum size that you can specify for max_size is 2 MB and the maximum is 2,147,483,647 TB. When UNLIMITED is specified, the file grows until the disk is full. Specifying a value lower than 2 MB will raise the error MSG_MAXSIZE_TOO_SMALL. The default value is UNLIMITED.
- MAX_ROLLOVER_FILES ={ integer | UNLIMITED }
Specifies the maximum number of files to retain in the file system in addition to the current file. The MAX_ROLLOVER_FILES value must be an integer or UNLIMITED. The default value is UNLIMITED. This parameter is evaluated whenever the audit restarts (which can happen when the instance of the Database Engine restarts or when the audit is turned off and then on again) or when a new file is needed because the MAXSIZE has been reached. When MAX_ROLLOVER_FILES is evaluated, if the number of files exceeds the MAX_ROLLOVER_FILES setting, the oldest file is deleted. As a result, when the setting of MAX_ROLLOVER_FILES is 0 a new file is created each time the MAX_ROLLOVER_FILES setting is evaluated. Only one file is automatically deleted when MAX_ROLLOVER_FILES setting is evaluated, so when the value of MAX_ROLLOVER_FILES is decreased, the number of files will not shrink unless old files are manually deleted. The maximum number of files that can be specified is 2,147,483,647.
- RESERVE_DISK_SPACE = { ON | OFF }
This option pre-allocates the file on the disk to the MAXSIZE value. It applies only if MAXSIZE is not equal to UNLIMITED. The default value is OFF.
- QUEUE_DELAY =integer
Determines the time, in milliseconds, that can elapse before audit actions are forced to be processed. A value of 0 indicates synchronous delivery. The minimum settable query delay value is 1000 (1 second), which is the default. The maximum is 2,147,483,647 (2,147,483.647 seconds or 24 days, 20 hours, 31 minutes, 23.647 seconds). Specifying an invalid number will raise the error MSG_INVALID_QUEUE_DELAY.
- ON_FAILURE = { CONTINUE | SHUTDOWN }
Indicates whether the instance writing to the target should continue or stop if the target cannot perform the write. The login issuing this must have the SHUTDOWN permission. If the logon does not have this permission, this function will fail with a MSG_NO_SHUTDOWN_PERMISSION message. The default value is CONTINUE.
- AUDIT_GUID =uniqueidentifier
To support scenarios such as database mirroring, an audit needs a specific GUID that matches the GUID found in the mirrored database. The GUID cannot be modified after the audit has been created.
A. Creating a server audit with a file target
The following example creates a server audit called HIPPA_Audit with a binary file as the target and no options.
CREATE SERVER AUDIT HIPAA_Audit
TO FILE ( FILEPATH ='\\SQLPROD_1\Audit\' );
B. Creating a server audit with a Windows Application log target with options
The following example creates a server audit called HIPPA_Audit with the target set for the Windows Application log. The queue is written every second and shuts down the SQL Server engine on failure.
CREATE SERVER AUDIT HIPAA_Audit
TO APPLICATION_LOG
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = SHUTDOWN);
