sysdtspackages table too big

[NWChowd]

I noticed that my msdb database is near 2GB, and, in particular, my sysdtspackages table is about 1.75Gb. the database I inherited has only 39 DTS Packages. I am running the SQL 7.0. Is the size large because so many versions of each of the DTS Packages has been saved?

Can I cut down the size of this table and database by deleting older versions of DTS Packages? can I run the following delete query:

DELETE FROM sysdtspackages
WHERE createdate >= '20040101'

And then maybe run DBCC SHRINKDATABASE


Does anyone see any problems with this?


Thanks,
DMill

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[JayKusch]

I have ran into this problem before. What I did was went into each DTS package and saved them out.

I then backed up the MSDB DB for a copy in case things went wrong, dropped the packages, truncated the sysdtspackages packages table, ran a DBCC shrinkfile, backed up the MSDB DB as a normal backup file and then reimported the saved DTS packages back in.

Thanks

J. Kusch

 

[mrdenny]

I never recommend making changes to system tables.

I would recommend doing this through the GUI. What happens if you have a DTS package that hasn't been updated in a while. Not to mention, I'm not sure if there are any ties from one version to it's parent, do deleting the rows may cause problems, that the client accounts for.

Personally I would spend the day clicking delete on the previous versions. And have the developers remove all there old versions every once and a while.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[NWChowd]

I only had 39 DTS Packages so I went in and manually deleted older versions using the GUI. I shrunk the sysdtspackages from 1.75GB to 116MB!!

If I had actually run the DELETE statement above, I would have deleted a few DTS Packages that had not been modified since laste 2003. Glad I didn't do that!!

My msdb database size still indicates 1.8GB though.
Is it okay to run DBCC SHRINKDATABASE or DBCC SHRINKFILE on the msdb database during production hours?

thanks!
DMill

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[mrdenny]

yeah, you can shrink any database. It will case a some slowdown, but nothing should really be using the msdb database except the agent, so no one should really notice a problem.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[NWChowd]

Great. I just ran DBCC SHRINKDATABASE and shrunk the msdb databse to 386MB from 1.75Gb.

thanks MrDenny and Kusch.

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================