Hall of Fame
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;
Hall of Fame
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)