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


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 (3)

  1. Anonymous says:

    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

  2. superlatch says:

    I'll dig out the SSIS package and post it here, sorry for the delay.