Project Server 2007 Published Database

[Joseph Cook]

Hello,

We are currently using Project Server 2007. I am responsible for the server and database maintenance. I've been watching the published database, and it is growing pretty quickly. When I look at the 'Disk Usage by Top Tables' report, the MSP_ASSIGNMENT_TRANSACTIONS table is by far the largest - 18.5GB w/ 22,064 rows. The next largest table is the MSP_PROJECTS table - 99.5MB w/ 631 rows. The MSP_ASSIGNMENTS table is 29MB w/ 11,543 rows. My question/concern is about the assignments_transaction table - should it be that large? When I query the table using SSMS, it takes several minutes to return all of the rows. I haven't been logging how fast it is growing on a daily basis, but over the last month it has grown from 9 or 10gb to the present 18.5GB.
Thanks
Joseph

EggHeadCafe - Software Developer Portal of Choice
Generic CAPTCHA Image Form Test
http://www.eggheadcafe.com/tutorial...9-f110e25ecf46/generic-captcha-image-for.aspx

 

[Ray McCoppin]

Joseph: I also have seen this table grow like this on project servers with
very active statusing on projects. Two things I can recomened is that you
archive you old completed projects and delete them from the published
database. Also in 2003 there was a admin page that you could delete
transactions older than a given age. You can do the same in PS 2007 but
there is not a web page for this. Deleting old project should clean this up.


Hope this helps
--
Ray McCoppin

http://www.randsmanagement.com
Project Server 2007 Archival Tool
SRS gantt charts

 

[Joseph Cook]

Hi Ray,

Thanks for your reply - it is very helpful. At least I know someone else
has seen a similar situation. We are currently archiving and deleting
projects as they close - maybe the ones with the most active transactions
just haven't closed yet. You mentioned a way to delete transactions older
than a certain time - is it just running a sql script to delete those
transactions? Do they just need to be deleted from the
msp_assignment_transactions table, or are there other tables that will need
to be updated as well? I haven't really noticed any problems due to the
large size, just want to stay on top of it and keep it under control.

Thanks,
Joseph