Read # of trace files

Question

Read # of trace files

Chitown

Hall of Fame

Points: 3390
More actions
January 26, 2023 at 3:28 pm

#4142045
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)

Begin 
    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
End
```
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*"
```

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

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

ratbak Ten Centuries Points: 1381 More actions · Answer 1

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;

Chitown Hall of Fame Points: 3390 More actions · Answer 2

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