The log-management plan I describe uses one scheduled SQL Server Agent job to execute the VBScript code that Listing 1 shows. The script first consolidates all files from the log-file directory into one master log file in the merge-file directory. Then, the script cleans up the master log file by deleting the old log files and sends an email notification signaling that it has successfully completed. Let's look at each section of the script in more detail.
Script Breakdown
At callout A in Listing 1, the code declares the local variables and object variables that the script references later. The code at callout B creates the master log file and opens it for appending. To make the master log filename unique, the script concatenates the current system date and time to the filename. By using a unique filename, you can run the log-management script multiple times throughout the day without overwriting any existing files. Note that you need to customize the Universal Naming Convention (UNC) directory reference \\SERVERNAME\SHARE, which is stored in both the dirLogFileDir and dirMergeDir variables, to fit your environment.
At callout C, the script iterates through each file in the package log file directory, reading any available files and appending them to the master log file. If no package log files exist, the script creates a master log file with one entry stating that no package log files were found. After appending the package log files to the master file, the script deletes the package log files. So the next time the package is executed, DTS will automatically create a new log file.
At callout D, the log-management script performs some file cleanup by deleting any master log files that are older than 7 days. This cleanup process minimizes the number of master log files and eliminates the need to manually delete old master logs. At callout E, the code uses the CDONTS API to send an email message notifying you that the merge script completed successfully. CDONTS is the object model that the Microsoft IIS SMTP service uses to send mail; it's automatically installed on Windows 2000 Servers running IIS 5.0 and the SMTP service. In addition to customizing the UNC path for the log files, you also need to customize the email addresses stored in the objMail object variable to match your environment.
Finally, at callout F, the code destroys all object variables that the script created. It's important to remember that when you're using an ActiveX script within a SQL Server Agent job, you must explicitly destroy all object variables by setting their values to nothing; otherwise, you can't save the job.
Troubleshooting Help
After you customize the log-management script to fit your environment, the last step is to create the SQL Server Agent job to execute the script. By coordinating the log-management script's schedule with the schedule of the DTS packages that are creating the logs, you can execute the packages multiple times throughout the day but only generate one log file per day. In addition, you can configure the SQL Server Agent job to send an email notification if the job fails. This notification, combined with the email notification built into the log-management script, provides you with an excellent method for monitoring the script's execution.
DTS log files can provide crucial information when you're troubleshooting DTS package errors and performance problems. I hope this article encourages you to use DTS package logging in your troubleshooting efforts and gives you some ideas for managing the size and number of your log files.