November 23, 2016 at 9:31 am
Hi All,
I have created a server level audit in my SQL Server 20212 v11.0.5058. The create script for this AUDIT is as follows:
CREATE SERVER AUDIT [Server Audit]
TO FILE
( FILEPATH = N'F:\AuditLogs\'
,MAXSIZE = 10240 MB
,MAX_FILES = 4
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '78fe0e53-564e-4913-be90-12527cb00d5f'
)
As you can see I have specified max filesize as 10GB and maximum of 4 files.
After a few months my SQL log contained many errors stating "SQL Server Audit failed to create the audit file make sure the disk is not full and SQL has the required permissions."
I could see 4 log files in the AuditLogs folder but the most recent one had a modified date of last month.
I then deleted some of the old .SQLAUDIT files manually and to my surprise a new one was created straight away.
So it seems like SQL doesn't have the necessary permission to delete old log files? Or do I need to specify an additional parameter when creating the audit to specify max_rollover_files or something similar?
Thanks in advance for any help you can provide.
Regards,
Phil
November 23, 2016 at 11:16 am
Looking at the MSDN page for CREATE SERVER AUDIT when you set MAX_Files, SQL does not roll over to the first file when the limit it reached, nor does it remove files.
If you re-create your audit and replace max_files with max_rollover_files, when it reaches the end of file #4, it will start over on file #1.
BUT before doing this, I would suggest a couple things:
1. Find out what your required retention policy is for the audit files
2. Verify that you can meet the requirements in #1 with the max_ setting you end up with
Regardless of which option you choose, nowhere in the page for create server audit does it indicate that SQL removes files (most likely so MS doesn't get involved in a "lost auditing file" issue.)
November 23, 2016 at 1:59 pm
Great, thanks Jasona I will give that a go with max_rollover_files 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply