One of the joys of working with SQL Server is the descriptive and meaningful error messages, right?
 |
| https://vignette.wikia.nocookie.net/batmantheanimatedseries/images/a/a7/DiD_39_-_Joker_Vision.jpg/revision/latest?cb=20160610021326 |
Exhibit A - the 33206:
Error: 33206, Severity: 17, State: 1.
SQL Server Audit failed to create the audit file 'T:\MSSQL\Audit Files\PLAND_Objects_DML_Audit_Events_A2B00B57-4B43-4570-93C8-B30EE77CC8C9_0_131645629182310000.sqlaudit'. Make sure that the disk is not full and that the SQL Server service account has the required permissions to create and write to the file.
At first glance, this error tells us one of two things is wrong - the disk is full or the SQL service account doesn't have the required permissions to create and write to a file at the given location.
Simple to troubleshoot, right?
Except when the service account already has Full Control to each folder and sub-folder in the path...
(Note that it is important to check every folder along the path - I have seen situations where the account has permissions to the direct sub-folder (in this example T:\MSSQL\Audit Files) or even the direct sub-folder and the root of the drive (T:) and yet a security check fails due to the intermediate folder not having the correct permissions. To me it shouldn't work this way, but sometimes it does...)
What about the drive being full?
Maybe not.
Having exhausted the obvious paths (at least obvious from the error text), I went back to my default next step:
|
| https://memegenerator.net/img/instances/52632802/i-see-your-google-fu-is-as-strong-as-mine.jpg |
A base search for a "SQL Error 33206" actually brought back a bunch of results about Error 33204 - "SQL Server Audit could not write to the security log" (FYI - the common fix for a 33204 is to grant the SQL service account rights to a related registry key
as described here)
A better Google contains a piece of the error message:
SQL 33206 "SQL Server Audit failed to create the audit file"
The second result in the resultset for this search is
a recent blog post from someone I know to be a reliable source, Microsoft Certified Master Jason Brimhall (
blog/
@sqlrnnr).
In his post Jason describes the same process I describe above - eliminate the obvious - but then he shows what the real problem was for my situation, the file configuration in the SQL Server Audit configuration:
When I checked the T:\MSSQL\Audit Files folder, sure enough, there were fifteen audit files reaching back over thirteen months' worth of service restarts.
To mitigate the problem I deleted the oldest of the fifteen files, and the Audit resumed.
WOOHOO!
The real fix to this situation is to configure the files as "rollover" files - setting the Audit File Maximum Limit to fifteen "Maximum Rollover Files" instead of fifteen"Maximum Files" would allow the audit to overwrite the oldest file once it reaches to configured max rather than crashing the audit as happened here.
Realize you can only do this if you can handle the oldest files being overwritten; if you have to persist the files you need to create a separate archiving process to handle that.
--
If nothing else, the real silver lining in my particular situation was that the creator of this audit at the client configured the audit to "Continue" rather than "Shut Down Server" (completely shut down Windows - not just stop SQL, but shut down the whole server) or "Fail Operation" (allow SQL to run but fail any operation that would meet the audit specification - not as catastrophic as "Shut Down Server" but still very impactful).
|
| https://www.thesun.co.uk/wp-content/uploads/2016/11/nintchdbpict000273355923.jpg?w=960 |
There are definitely situations that call for "Shut Down Server" or "Fail Operation" - if your Audit is in place to satisfy a legal/regulatory/moral requirement, then definitely consider these options - but this is often not the case.
--
Hope this helps!
