Share via

Using an SSIS package to monitor and archive the default trace file

  • Article

I find it frustrating that the SQL Server 2005/2008 default trace is continually overwritten and there is no way to store x number of files or x MBs of data.  As a workaround, I developed an SSIS package to monitor the \LOG folder and automatically archive the default trace file whenever a new file is created.

This consists of a FOR LOOP container, a Script Task and a File System Task plus a whole bunch of variables and property expressions.

The guts of the package is really in the Script Task as this is where I use a WMI query to monitor the \LOG folder for .trc files.  The file is then renamed (date-time-servername-file) to another folder\share which can be a UNC structure e.g. \\server\share.  This way I have a permanent record of the basic server activity for root cause analysis/troubleshooting. 

The screenshot below shows the basic structure of the package.

image

Comments

  • Anonymous
    May 13, 2009
    PingBack from http://asp-net-hosting.simplynetdev.com/using-an-ssis-package-to-monitor-and-archive-the-default-trace-file/
  • Anonymous
    June 19, 2012
    Hi Benjamin,Currently I am in the same situation, though my SSIS skils are limited as I am junior, could you possibly provide a little more informationas to the scripts executed and variables used? I'm sure we can exchange e-mails if you would like, thus once I complete my package I will update you on anything that I came across... I am looking at a SQL Server 2005 instance for a start.Regards,Anthony
  • Anonymous
    June 26, 2012
    I'll dig out the SSIS package and post it here, sorry for the delay.