March 9, 2004 at 3:42 pm
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.
March 9, 2004 at 4:17 pm
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 )
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply