x

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
more ▼

asked Oct 26, 2010 at 04:50 AM in Default

Håkan Winther gravatar image

Håkan Winther
15.8k 35 37 48

How hard is it to type/think in English and Swedish?
Oct 26, 2010 at 07:32 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Oct 26, 2010 at 05:01 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

You could also have some sort of scheduled delete job running to be on the safe side.
Oct 26, 2010 at 06:56 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Dec 02, 2010 at 11:53 PM

Håkan Winther gravatar image

Håkan Winther
15.8k 35 37 48

Excellent - glad you got a solution sorted out.
Dec 03, 2010 at 12:37 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x43

asked: Oct 26, 2010 at 04:50 AM

Seen: 2220 times

Last Updated: Oct 26, 2010 at 05:06 AM