question

I have created a server audit that creates files with max size 50MB, and they only want to keep the last 10 audit files. The problem is that the audit file are not deleted automaticly. Here is the script to create the audit: CREATE SERVER AUDIT [DB_object_Audit] TO FILE ( FILEPATH = N'E:\DWH\SQLAudit\' ,MAXSIZE = 50 MB ,MAX_ROLLOVER_FILES = 10 ,RESERVE_DISK_SPACE = ON ) WITH ( QUEUE_DELAY = 2000 ,ON_FAILURE = CONTINUE ,AUDIT_GUID = '7f4a66d8-687e-4df3-b3f9-dc73055e4bca' ) GO A scheduled job stored "last accessed date" from the audit files for each object in a database and the performance was terrible after 20 days, and I realized all the files are still there (50Gb). Shouldn't the log file be replaced? This is whats written in MSDN : > Specifies the maximum number of files > to retain in the file system in > addition to the current file. The > MAX_ROLLOVER_FILES value must be an > integer or UNLIMITED. The default > value is UNLIMITED. This parameter is > evaluated whenever the audit restarts > (which can happen when the instance of > the Database Engine restarts or when > the audit is turned off and then on > again) or when a new file is needed > because the MAXSIZE has been reached. > When MAX_ROLLOVER_FILES is evaluated, > if the number of files exceeds the > MAX_ROLLOVER_FILES setting, the oldest > file is deleted. As a result, when the > setting of MAX_ROLLOVER_FILES is 0 a > new file is created each time the > MAX_ROLLOVER_FILES setting is > evaluated. Only one file is > automatically deleted when > MAX_ROLLOVER_FILES setting is > evaluated, so when the value of > MAX_ROLLOVER_FILES is decreased, the > number of files will not shrink unless > old files are manually deleted. The > maximum number of files that can be > specified is 2,147,483,647. Below you > have a procedure to collect the > information from the audit files. As > you can see, sys.fn_get_audit_file > will look at every file in the > specified path. Do I need to remove > the file manually. CREATE PROCEDURE [META].[spObjectAccess_Update] @dbname AS SYSNAME AS --meta.spObjectAccess_update 'DWH' DECLARE @path varchar(255) SELECT @path=DWH_Toolkit.DWH.logPath() MERGE [META].[ObjectAccess] AS target USING ( --Hämta samtliga object och de senaste loggad händelserna för respektive object SELECT DISTINCT t.object_Id AS objId, schema_name(t.schema_id) AS schemaName, t.name AS objName, t.Type COLLATE database_default AS objType, convert(date,max(aud.event_time)) AS lastAccess FROM sys.fn_get_audit_file (@path + 'SQLAudit\*',default,default) aud RIGHT OUTER JOIN sys.objects t ON aud.object_id=t.object_id AND aud.schema_name 'sys' AND aud.database_name = @dbname WHERE t.is_ms_shipped =0 AND t.type in ('U','P','V','IF', 'FN','TF') GROUP BY t.object_id, t.schema_id, t.name, t.type ) AS source (objid, schemaName, objName, objType, lastAccess) ON ( target.schemaName = source.schemaName AND target.objName = source.objName AND target.objType = source.objType ) WHEN MATCHED AND source.lastAccess IS NOT NULL AND ( source.lastAccess>target.lastAccess OR target.lastAccess IS NULL ) THEN --Händelse har loggat för detta objekt, uppdatera UPDATE SET target.lastAccess = source.lastAccess, target.objID = source.objID WHEN NOT MATCHED BY TARGET THEN --Detta objeckt finns inte sedan tidigare, lägg till INSERT ( objId, schemaName, objName, objType, lastAccess ) VALUES( source.objId, source.schemaName, source.objName, source.objType, source.lastAccess ) OUTPUT $action, Inserted.*; GO