Truncate DTS logs

• mssql_rules

  SSCrazy

  Points: 2783

  More actions

  March 9, 2004 at 3:42 pm

  #86252

  A query that deletes the log entries that are older than the last 10 runs.

  Since some packages may run several times a day, and some run once few days, I want to keep

  history of the latest 10 runs. Here is what I came up with that deletes the log entries for a

  DTS package name provided. I would like to modify this for all the DTS packages. Is there any

  way to accomplish this without a cursor?

  DELETE  FROM sysdtspackagelog

  WHERE NAME  = @PackageName AND  endtime <

  (SELECT MIN (endtime)

  FROM

  (SELECT TOP 10 * FROM sysdtspackagelog

  WHERE NAME  = @PackageName

  ORDER BY endtime DESC ) AS B )

   

  Thanks.

• mssql_rules

  SSCrazy

  Points: 2783

  More actions

  March 9, 2004 at 4:17 pm

  #498074

  This is what I came up with. Any better SQLs out there?

  DELETE pl FROM sysdtspackagelog pl

  WHERE lineagefull NOT IN ( SELECT TOP 10 lineagefull FROM sysdtspackagelog pl2

  WHERE pl.name = pl2.name

  ORDER BY endtime DESC )