SQL Audit - Capture file names from the audit RRS feed

  • Question

  • Fellow SQLers,

    Using SQL 2016 Standard.

    I have setup a SQL Audit with max rollover set to 4.  I do understand that if someone is queriering the files, the files become locked. if during that time, the audit needs to roll beyond the limit, it deletes first then adds. But it cannot becuause the files are locked so the failed delete is silent and you can end up with more files than you expect.

    I am trying to capture how many files are on the system for the audit. Kind of a safe guard.

    One way was just running a count(distinct file_name)) from fn_get_audit_file with powershell. I was for looking something more elegant where it can be run from another server (did not want another job on the host). Is there a work around for this?


    Friday, August 30, 2019 4:22 PM

All replies