Read # of trace files

  • I have a SQL agent job which starts the trace (for audit purpose) everyday at 7 am and it stops at 10 pm. Trace generates files which are 500MB in size but no limitation is to how many the trace can generate. I have seen as high as 185 files....

    I have another agent job which runs and it reads the data from the file into an Audit table (code is below)

    Wondering if someone can help me modify the code to be more logical...

    Truncate table Audit

    Declare @filename varchar(100)
    Declare @filelocation varchar(100)
    declare @i int

    Set @filelocation = 'D:\Audit\'
    While @i < 60 (I manually change it since some days # of files are 100, 120 etc., but would love to automate so I know exactly how many files I have from the trace)

    select @filename = @filelocation + 'Audit_01_20_2023_' + cast(@i as varchar(3)) + '.trc'
    Insert into Audit
    Select * from ::fn_trace_gettable(@filename, 1)
    Set @i = @1 + 1

    I also have another step which deletes files which are older than 2 days. This doesn't work either since it delete all the files, including the current ones.

    FORFILES /p D:\Audit /D -2 /c "cmd c/ DEL Audit*"



  • We use xp_delete_file  with something like this:

    declare @daysToKeep int = 3
    declare @minDateToKeep date = dateadd(day,-daysToKeep ,sysdatetime());
    declare @dateString char(10) = convert(char(10),@minDateToKeep,121);
    declare @traceFilePath varchar(30) = 'D:\Audit\';

    EXECUTE master.dbo.xp_delete_file 0,@traceFilePath, N'trc',@dateString;
  • Unfortunately it didn't work for me since I believe master.dbo.xp_delete_file only works with backup and some other files but not with trace files

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply