May 3, 2005 at 12:16 pm
Does anyone have a automatic way to remove older versions of DTS from sysdtspackage? I would like a way to automatically remove any version other than the 3 most current version of each DTS.
May 4, 2005 at 7:56 am
Someone on this site posted this solution a while ago. I copied it for possible future use but never tested it so I don't know if it actually works or not. I also don't know if it can be adapted to leave you with X versions.
Hope it helps
Teague
REMOVING OLD VERSIONS OF DTS PACKAGES (From SQLServer Central)
This has not been tested by me.
You need to put it in the msdb database.
CREATE PROCEDURE ip_RemoveOldDTSVersions
AS
delete
sysdtspackages
where
versionid in (
select
versionid
from
sysdtspackages
inner join
(
select
[id] mxid,
max(createdate) as mxcd
from
sysdtspackages
group by
[id]
) as MaxCD
on
[id] = mxid and
createdate != mxcd
)
go
There are also logging tables associated with DTS packages which can be removed
There are 4 of them: sp_dump_dtslog_all,
sp_dump_dtspackagelog,
sp_dump_dtssteplog and
sp_dump_dtstasklog.
Use the resultset from the query to feed the last three procedures
May 4, 2005 at 10:07 am
-- Table to Hold most recent versions
Declare @T table (versionid uniqueidentifier)
-- Most recent
insert into @T
Select p.versionid from sysdtspackages p
join (
Select Id, max(createdate) createdate
from ( Select * from sysdtspackages where versionid not in (Select versionid from @t)) x
group by Id
) c on p.id =c.id and p.createdate = c.createdate
-- Most Recent - 1
insert into @T
Select p.versionid from sysdtspackages p
join (
Select Id, max(createdate) createdate
from ( Select * from sysdtspackages where versionid not in (Select versionid from @t)) x
group by Id
) c on p.id =c.id and p.createdate = c.createdate
-- Most Recent - 2
insert into @T
Select p.versionid from sysdtspackages p
join (
Select Id, max(createdate) createdate
from ( Select * from sysdtspackages where versionid not in (Select versionid from @t)) x
group by Id
) c on p.id =c.id and p.createdate = c.createdate
-- Remove others
delete from sysdtspackages where versionId not in (Select versionid from @t)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply