On twitter today TJay Belt (blog|twitter) asked if anyone had concerns with enabling logging on DTS packages. This brought to mind some of the logging fun that I have had with SQL 2000 and DTS over the years.

With DTS you have the option of logging to a flat file and also to SQL Server directly.

When choosing the SQL Server option and entering your server name you’ll find that logs are written to msdb.dbo.sysdtspackagelog and individual steps within those packages go to msdb.dbo.sysdtssteplog.

 Viewing DTS Package Logs

Looking at the SQL Server stored log is as simple as right clicking on the DTS package itself and select “view log” from the list. I have noticed in the past that this can actually cause significant blocking issues within the msdb database. This process never seemed very intelligent as it appeared to scan all the logs for all packages and then filter out what it didn’t want.

Frequently you are just looking for failures, for this I have a small script which can be run, it returns packages with steps that failed and the failure information for that step. You can limit it to whatever time frame you want, as well as limit it to certain packages.

select 

   PL.Name

   , PL.starttime

   , PL.endtime

   , SL.stepname

   , SL.errordescription 

from 

   msdb.dbo.sysdtspackagelog PL(nolock)

      inner join msdb.dbo.sysdtssteplog SL (nolock)

        on PL.lineagefull = SL.lineagefull

where 

    --name = '<job name>' and 

   stepexecresult <> 0

   --and starttime > '2009-06-08 00:00:00' and starttime < '2009-06-09'

order by 

    PL.starttime desc

Keep Your Logs Clean

Something else to keep in mind is that your DTS logs will not clean themselves. It’s worthwhile every week or two to clean up old data. Both sysdtssteplog and sysdtspackagelog have start time and end time columns making any deletes a quick and easy process.