July 21, 2011 at 9:47 am
Created the following Audit
CREATE SERVER AUDIT [ServerAudit - DB Structure Changes]
TO FILE
( FILEPATH = N'F:\DB Audit Logs\'
,MAXSIZE = 200 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'a6e52b0c-9608-4f5a-af5a-3fa7e0cf690f'
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification - DB Structure Changes]
FOR SERVER AUDIT [ServerAudit - DB Structure Changes]
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
The Server Auditing is working correctly in that it is capturing the correct information. When I view the the audit information through the "Log File Viewer", it only returns 1000 records and there are no filters enabled on this window.
It has the appearance that it will only contain 1000 records, and will continue rolling over older entries.
Is there a flag/setting somewhere to increase the number of records stored in the audit file?
August 4, 2011 at 9:29 am
The audit file will store more than 1000 records, it's just that the log file viewer will only display 1000 records. I don't think there's any way to change that behavior. I'd recommend using the sys.fn_get_audit_file function to view your audit files.
Colleen
September 6, 2011 at 1:50 am
hi,
i know this may sound stupit to ask, but how will you retrieve audit data from this?
September 6, 2011 at 11:33 am
From the audit file? As per BOL, you just need to select from a function.
SELECT * FROM sys.fn_get_audit_file ('\\serverName\Audit\HIPPA_AUDIT.sqlaudit',default,default);
GO
-Roy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply