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.
- 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.