Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SQL SERVER AUDIT Expand / Collapse
Posted Monday, December 2, 2013 12:25 PM


Group: General Forum Members
Last Login: Yesterday @ 2:00 PM
Points: 498, Visits: 2,839
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???
Post #1518994
Posted Monday, December 2, 2013 12:43 PM

SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 620, Visits: 583
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.

Post #1519001
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse