Project Server Database grows to unreasonable size

[Gizmo Gizmo]

Hi!
My Project server database grows rapidly when importing timephased data and
i am worried about if something is wrong?

I have developed a program in VBA that imports projectdata that i am going
to use for a customer that is underway of migrating from another (than EPM)
Project Management application. The customer has a lot (200+) projects he
wants imported to EPM. I should note that this is a onetime conversion. Each
of the projects has actuals for each assignment that is to be imported on a
monthly basis.

A short (rough) description of the solution;
1. The VBA code is run from within a blank project i Project Profession that
when connected to Project server.
2. Reads tasks and assignments from a excel-file that holds the exported
data. Creates tasks, resources and assignments i Project.
3. For each of the assignments loops through assignment timescale data and
updates it with the data from Excel.
4. Project get published to the server.

My problem is in step 4 and 5:
Project Server database grows to a size that worries me alot. It also takes
a lot of time for Project server to save the project which will be a problem
when we are going to convert all projects.

Thing is that assignment span over a long period of time, ususally several
month and sometimes several years.

Initial size of Project server database is about 21 MB. After i've imported
just one (test)project with about 110 task with totally 400 assignments the
database has grown to 65+ MB. Transaction log file also grows comparingly.

I have concluded that Timephased data is stored in table
MSP_View_Proj_Assn_TP_By_Day and MSP_View_Proj_Task_TP_By_Day. Import adds
80,000 rows in those two tables! The reason for this growth is that each day
with timephased data becomes one row in database!

Since I've little experience of this kind of import I am confused if this is
normal? Is there another problem or is it so that 80,000 rows makes up to
about 65 MB? Also if there are some way to decrease projects way of storing
timephased data?

I use Project 2003.

Please, anyone have experience on this?

 

[Gary L. Chefetz [MVP]]

Gizmo:

First I'm going to suggest that you ask your developer questions at
microsoft.public.project.developer where you're more likely to get answers.
If you're updating timephased data directly, you're making a mistake. Let
Project do this as it knows how to do it efficiently. You should be updating
actual work only.

After importing your projects, you'll want to force a binary rebuild for
each project to reduce any bloat created by your import process. See the
projectserverexperts.com FAQ for info on how to do this.

 

[Gizmo Gizmo]

Hi Gary. Thanks for a fast reply.

Surely I will post my questions on project.developer in the future. Sorry
about that.

I am not quit sure I understand your point on updating timescaled data
directly. I should make clear that I do not make updates on tables in DB
directly. Since I want to update actual work (and actual costs) month by
month that is what I do. But on the right month which I access through
timescale data shown in the codesnippet below.
---------
Set tsvActW = .TimeScaleData(dtMin, dtStatus,
pjAssignmentTimescaledActualWork, pjTimescaleMonths)

For CurrMonth = 1 To tsvActW.Count
tsvActW(CurrMonth).value = WorkValue
Next
---------
I don't know how to update actual work only for a certain month any other
way. Could you please give me more details on that.

Also, thanks for your tip on how to force a binary rebuild. I tried it but
it had no effect on size of database unfortunately.

/Gizmo