HUGE Reporting database

[Mike]

We have a Project Server 2007 instance setup with a published database
weighing in at a little less than 600MB. The reporting database,
however, is a whopping 32GB (yes, GB). This was discovered when weird
things started happening and the applicaton pretty much stopped as a
result of the disk have been filled. We did a backup and truncated
the logs and ended up freeing up about 50GB on the disk. That was
last night. Then, this afternoon, we ran into the same issue again.
This time the Reporting database is about 38GB and again the
transaction logs maxed out the disk.

The only activity has been creating approximately 50 projects (over
the last 2 days), each with about 150 task lines each. There is no
other resource activity, timesheets, or task update submission/
approval. In fact, there are only maybe 2-3 users in the system as of
now.

We have since converted the recovery model down from full to simple in
efforts to keep the PWA application and custom reports operational.
This has provided the extra room to breathe, but the db size is still
way out of control. In all of our other implementations, the
reporting database is a little less than the published database and we
would be expecting this to be as well.

There has been nothing custom written into this database apart from
PWA custom fields, etc. The custom reports I mention above are simple
SSRS reports querying from this database (stored procs, functions, etc
are in a separate db). Does anyone have an idea why this reporting
database is so big? And, what options are there to fix this?

 

[Paul Conroy]

To get a full picture of what going on in the Reporting Database you could
run a SQL trace, but deciphering the trace could prove to be a challenge.

Have you tried re-building the Reporting Database ? Backup/Restore the
custom fields will initiate this process. Be aware that might take some time,
so I'd suggest doing this during a quiet period.

--
Did this post help you. Consider passing on the good will by making a
donation this great charity.
http://www.fundraiseonline.co.nz/TheProjectServerGuru/

http://theprojectserverguru.spaces.live.com

 

[Gary L. Chefetz [MVP]]

Mike:

Does your system have SP1 installed? Is the temp db the cause of the bloat?
Are you building a cube each night? If so, what settings are you using?

--

Gary L. Chefetz, MVP
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com

 

[Mike]

Ok, I figured out what was causing this huge reporting database.
Seems there was a bit of work being done in creating some old
historical projects. As a quick background, there is an import
process (macro) that brings in some timephased financial data that
will alert the PM if this data falls outside of a task's date range.
To prevent this (in an attempt to accelerate the loading of these
projects), massive durations were used. Massive in the ballpark of
5,000 days for about 200 tasks each. Well, as these projects get
built in the reporting database, the 2 tables "MSP_EpmTaskByDay" and
"MSP_EpmAssignmentByDay" get lots and lots of records. Oh yeah, about
80 projects were imported, all using a similar template. After
deleting these projects from the published database, the reporting
database did shrink down to about the same size as the backup from
just before all this happened. Their template has since been modified
and a new historical import process has been implemented minimizing
the task durations. The database is a much more managable size now.
Who knew..

 

[Gary L. Chefetz [MVP]]

Mike:

Nothing fills a project database faster than 5000 days of timephased
assignment data! Glad you found the source.

--

Gary L. Chefetz, MVP
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com