Couple of days ago I’ve been asked by a customer to preserve the Reporting Services trace log files. Reporting Services provides three trace log files, which are located at Microsoft SQL Server\Reporting ServicesLogFiles. Trace logs are created daily, starting with the first entry that occurs after midnight (local time). The local time of the computer is used for naming the trace log. By default, trace logs are deleted after fourteen days.

The solution is quite simple. In SQL Server 2000, I created a scheduled job that uses an ActiveX script task. In the VBScript, I use the FileSystemObject object that comes with Script Runtime library (check the documentation). All RS trace log have the date of creation included in the name. The only trick is to extract the date from the file name, compare it with the actual date, and finally move or copy the file to some destination folder, before it is deleted. Here I am including the script.

Option Explicit

Dim objFSO, strFolder, objFolder, objFile, colFiles

Dim strFileDate, dtFileDate, strForDelete

 

Set objFSO = CreateObject(“Scripting.FileSystemObject”)

strFolder = “C:Program FilesMicrosoft SQL ServerMSSQLReporting ServicesLogFiles”

Set objFolder = objFSO.GetFolder(strFolder)

 

Set colFiles = objFolder.Files

If colFiles.Count > 0 Then

   For Each objFile in colFiles

       strFileDate=Mid(objFile.Name,InStr(objFile.Name,”.log”)-13,4) & “-” & _

                   Mid(objFile.Name,InStr(objFile.Name,”.log”)-19,2) & “-” & _

                   Mid(objFile.Name,InStr(objFile.Name,”.log”)-16,2)

       dtFileDate=CDate(strFileDate)

       If dtFileDate < Date – 7 Then

          objFile.Move(“C:Temp” & objFile.Name)      ‘Insert destination folder here

       End If

   Next

Else

   WScript.Echo(“No files to move in the log folder ” & objFolder.Name)

End If

 

      

Set objFSO = Nothing

Set objFolder = Nothing

Set colFiles = Nothing

Dejan Sarka