It’s Great When No One Asks Questions
You’re taking the best steps to track what goes on in your instance – that’s awesome, even in heavily regulated industries which require these things, no one ever looks at them. Having been part of audits for years (and thankfully not involved for almost the last decade) I have yet to see how this has helped anyone. It’s kind of like how the audit required a firewall, and Windows firewall technically checks the box. I don’t know why we have people making decisions about this stuff that can barely tie their shoes… diatribe over.
Getting back on track instead of listening to me complain, many DBA’s and internal security folks like writing to the Windows Security Event Log because the word security is in the name and they have some tool like Splunk that automatically collects these details. I like automation, so that’s a nice win. However, you may be running into SQL Server error 33204 which is a failure to write an audit event to the security event log. If you trace this down, you’ll notice that Windows is giving back OS error 8 to SQL Server, which translates to, “Not enough memory resources are available to process this command.” This isn’t exactly what you think, it’s not that the server has run out of memory, it’s that there are too many incoming requests to log events. This is one of those, the programmer who worked on this part of the code decided this was the best error message to return, and it very well might be… I don’t know what the code looks like inside of Windows there (and I’m not going to reverse it), so it might be that the number of free buffers or whatever they use had run out, thus out of memory. Could it be a better error? Maybe.
Too Much of a Good Thing
If you’re getting OS error 8 back, there is a high likelihood (as in almost always that I’ve worked on, anyway) that the number of audit events generated is just too high. The Security Event Log can’t keep up with the incoming volume. How many is too many? It somewhat depends on the hardware for the server (a 1 cpu 2 GB OS instance isn’t going to be able to ingest at the same rate as a 24 core 1 TB OS instance), but on a decently sized machine that isn’t being railed to 100% cpu, context switched to death, oversubscribed VM, or out of memory (read: healthy-ish server), then an audit generation rate of 100-300 events per second can generally be obtained.
Half is More
If you’re wondering what you can do to fix/tune this, the answer is…. Reduce the number of audit events or don’t log to the Security Event Log. It’s simple, luckily! There’s no magic bullet or server setting, here, just good old fashioned close the flood gates.
1 thought on “Auditing To the Security Event Log – SQL Server Error 33204 and OS Error 8”
Comments are closed.