SQL Server 2008 R2 – Find Routine Running Daily SQL Trace File

We had another mysterious SQL crash yesterday afternoon (no logs for that one either--just a SQL error message). We failed over to the mirror and ran some burn-in tests on this system and it turns out this server had a memory module that must have recently developed issues. I believe this is likely the cause of these issues. Thanks to all who looked into this!

I believe it has to do with the timing and interpretation of System Variables.

Using this expression to build a filename with _.log I had been using:

@[User::PathROOT]+"edw_logs\\"+ @[System::PackageName] + "_"+(DT_STR,4,1252) YEAR(GETDATE())+ RIGHT("00"+(DT_STR,2,1252) MONTH(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252) DAY(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252)DATEPART("Hour", @[System::StartTime]),2) + RIGHT("00"+ (DT_STR,2,1252)DATEPART("Minute", @[System::StartTime]) ,2) + RIGHT("00"+(DT_STR,2,1252)DATEPART("Second", @[System::StartTime]),2) +".log"

and two files were created once on validation and once when it ran.

Using just User variables, they are evaulated at runtime and only one file is created. See this:

@[User::PathROOT]+"edw_logs\\"+ @[User::MyPackageName] + "_"+(DT_STR,4,1252) YEAR(GETDATE())+ RIGHT("00"+(DT_STR,2,1252) MONTH(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252) DAY(GETDATE()),2)+RIGHT("00"+(DT_STR,2,1252)DATEPART("Hour", GETDATE()),2) + RIGHT("00"+ (DT_STR,2,1252)DATEPART("Minute", GETDATE()) ,2) + RIGHT("00"+(DT_STR,2,1252)DATEPART("Second", GETDATE()),2) +".log"