Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 395, Visits: 1,993
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: Wednesday, August 13, 2014 2:54 PM
Points: 611, Visits: 507
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