SQL SERVER AUDIT

  • We have setup an audit on one of our prod server and collect the audit files on a drive. We have a sql server job which runs every 10 mins and load the audit data from files to a table. The files never get deleted as set by max_rollover_files setting. This causes files to pile up and the job tries to load the data into a table takes too long(7 to 8 hrs) to load the data as it scans all the files in that folder. Whenever we have to delete the files we have to stop the audit for few seconds. If we don't stop and try to delete the files it throws an error saying the files cannot be deleted as they are in use.

    Any suggestions???

  • Have you considered using the script that's reading the files into the table to then move said file to an archive folder? Powershell may help you with this if it's an easier answer.

    get-childitem -path "\\srv1\t$\tracefiles\*.trc" | move-item -destination "\\srv1\t$\tracefiles\Archive\"

    and just have that be step 2 in the process. Someone step in if you don't mind, I know you can set it to move based off a where... and have it in the load of the files set the archive bit, and then use the powershell script to only move those with the archive bit swapped.

    For the issue where the file is in use and cannot delete. If it only reads in files without the archive bit... but sets it at the end, you wouldn't have to worry about the old files being picked up.

    This is just for an idea path, I didn't provide a complete solution. 😀

    .

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply