question

Håkan Winther avatar image
Håkan Winther asked

Server Audit files are not deleted

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
sql-server-2008audit
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

How hard is it to type/think in English and Swedish?
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
Håkan please check out [this article on MSDN][1] If you search for "rollover" it should jump to about 1/3 the way down the page where it explains that SQL Server will try to delete the oldest file as stated in your `MAX_ROLLOVER_FILES` setting. ***If it is unsuccessful, it will continue silently!*** I would check that the correct privileges exists for the file location and that nothing else is going wrong. It says it will continue silently, but maybe you have some sort of error log entry too. [1]: http://msdn.microsoft.com/en-us/library/dd392015(SQL.100).aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You could also have some sort of scheduled delete job running to be on the safe side.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
Thanks WilliamD! You lead me in the right direction. It looks like the audit files are too small (50MB) for the current load, I think new files are created faster than MAX_ROLLOVER_FILES setting is evaluated, and the audit will only delete one file at the time. I increased the size of the audit files and added a manual delete to make sure this issue never will appear again. As William pointed out: > "If it is unsuccessful, it will > continue silently!" How crazy is that? A warning would have been a nice feature! I think I should make a review to make sure I don't audit more than neccesary.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Excellent - glad you got a solution sorted out.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.