January 26, 2023 at 3:28 pm
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*"
January 26, 2023 at 5:13 pm
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;
January 26, 2023 at 5:55 pm
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