How does the QUEUE_DELAY option for Server Audits work

I am working through how SQL Server Audits work and would like some clarity on how the QUEUE_DELAY options works. BOL defines the options as:


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.

Where are these actions queued tempdb, a system table, etc? Can I query that location to see how many items are in the queue? If the ON_FAILURE option is set to shut down the server, will these items be logged to the audit once the server is restarted and the location can be accessed again?

SQL Server Audit leverages the new Extended Events architecture introduced in 2008.

If you look at the following DMVs, you can see the related structures appear when the audit is started.

select * from sys.dm_server_audit_status
select * from sys.dm_xe_session_events
select * from sys.dm_xe_session_targets

Note that audits have their own set of special targets, which cannot be used directly through Extended Events. If you start generating some audit events, you'll see the events show up in the actual target, but unlike a ring buffer target, they don't appear in the target_data column in the DMV for security reasons.

Having said that, it's a bit unclear (and apparently undocumented) how the Audit session targets work internally. Based on the fact that Audit is built on Extended Events, and given the behaviour options for when auditing fails, I assume the Audit targets are simply modified ring buffer targets that don't expose the event data back to SQL Server. While this explanation makes the most sense to me, I'm honestly not 100% sure. Hopefully someone with more internals knowlege can comment.


Can I query that location to see how many items are in the queue?

Not as far as I can tell; again, likely for security reasons.


If the ON_FAILURE option is set to shut down the server, will these items be logged to the audit once the server is restarted and the location can be accessed again?

There are many scenarios wherein the event could not be recorded. If my assumption about hitting a memory buffer is correct, the failed event(s) will be dropped on the floor as a shutdown will release all SQL Server memory. Anything that didn't make it to disk will be lost.

StackExchange Database Administrators Q#22502, answer score: 6