Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KHeon
Posting Yak Master
135 Posts |
 Posted - 2002-05-22 : 14:14:35
|
| Hello!I'm just curious to find out if there is a way to purge the version history of a dts package. I know the data is stored in the sysdtspackages table but I don't really wish to directly manipulate the system tables. Are there any built in stored procedures (similiar to sp_delete_backuphistory) that will purge version information for a package (or set of packages)?Additionally, somewhere I'd seen that a packages run history is also stored. This would also be something I'd like to know how to purge, as I'd imagine frequently scheduled packages will start to fill up the database.Any ideas? Thanks!Kyle HeonPixelMEDIA, Inc.Senior Application Programmer, MCPkheon@pixelmedia.com |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-22 : 16:37:21
|
| You can use sp_purge_jobhistory to clear the run history, but the DTS package versions need to be deleted manually, AFAIK. |
 |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-05-22 : 16:38:48
|
| There is probably an undocumented function to do this. If you right click on a package and select versions, a dialog that lists of all versions is displayed. There is a delete button on this dialog. If you run a profiler trace while clicking this button you'll be able to see how EM clears a version history row.Jeff BanschbachConsultant, MCDBA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-22 : 16:50:30
|
| I found it! In Books Online, under "Building SQL Server Applications - DTS Programming" there's a RemoveFromSQLServer method of a DTS Package object. You need to supply it with everything though; the package name, the version GUID, etc., so I don't know how much you can do automatically. I think you'd have to enumerate all of the version GUIDs, match them up to their create date, then remove everything except the most recent. |
|
|
|
KHeon
Posting Yak Master
135 Posts |
Posted - 2002-05-23 : 07:18:31
|
| Thanks guys, that'll get me started. Just trying to put in place measures that keep the database clean of data that we really don't need. I recently found out about the sp_delete_backuphistory system proc and when I used it on one of our prod machines, giving a date months back it took awhile. I had to slowly the proc, handling about a month of data at a time so that I didn't add to much load to the server, but now I have a job scheduled to run monthly that truncates anything older then a month...next up, DTS package cleanup.Again, much appreciated!Kyle HeonPixelMEDIA, Inc.Senior Application Programmer, MCPkheon@pixelmedia.com |
|
|
|
|
|
|
|
|
Truncating DTS Package History