Save-As/Overwrite Questions

[Erik K]

Hi,
I have a need to periodically overwrite existing enterpise projects from
..mpp files coming in from outside the enterprise. The nature of the
installation does not allow access from the outside, and deleting/re-adding
is not an option. Everything works fine, but I started noticing that my
Project Server databases were growing very large. I poked around and saw that
the _shadow tables in the draft database contain sets of data from the
overwritten project that correspond to each time the project was overwritten.
It leads me to wonder what else is being persisted throughout the database(s)
and what event would trigger Project Server to clean this stuff up. BTW, I am
on 2007 SP2.
Thanks,
Erik

 

[Stephen Sanderlin [MVP]]

Erik,

The various _Shadow tables are used by WinProj's caching feature to sync
up changes (specifically deletions) between the client and the server.
Essentially, these tables provide the mechanism by which a client can
ascertain whether a delta between its locally cached data and the data
stored on the server should be treated as new or ignored as deleted.
These tables store the PKs of a deleted row, the revision counter
increment when the data was deleted, and the datetime of the deletion.

To the best of my knowledge, there is no supported way to clear this
data out from the database(s) without deleting the associated object
(e.g. project, custom field, lookup table), because by deleting the
_Shadow data you would be assuming (perhaps incorrectly) that there is
no client out there that has an aged copy of the data in its local
cache.

HTH,
Steve

--
Stephen Sanderlin, Project MVP
VP of Technology
MSProjectExperts

For Project Server Consulting: http://www.msprojectexperts.com
For Project Server Training: http://www.projectservertraining.com

Read our blog at: http://www.projectserverhelp.com

 

[Erik K]

Hi Steve,
Thanks for the explanation. Since I am overwriting the entire project,
everything gets deleted and re-added, which accounts for what I am seeing. If
I understand you correctly, these tables track all deletions... for the life
of the project, and deleting the rows in the these tables (in addition to
being unsupported) would tell project pro that it's current cache is
up-to-date.
Thanks again.
Cheers,
Erik

 

[Stephen Sanderlin [MVP]]

No problem -- happy to help! The reason for the deletions depends on how
you are overwriting the project... if you're going through the project,
deleting all the tasks, then repopulating it with data from the external
MPP file, then that counts as a deletion, because the
tasks/assignments/etc. will have different UIDs.

Otherwise, your interpretation of my response is correct. It's not so
much that WinProj will think that it's cache is "current" as much as it
is that if there is a task that exists in the cache but not on the
server, WinProj won't know that it was deleted and will consider it to
be a new task. Because it's a new task, when the Project is re-saved,
WinProj will load it back onto the server.

Consider the following scenario, and you will see the problem:
1. PM1 creates project and saves.
2. PM2 opens PM1's project.
3. PM1 deletes a bunch of tasks from the project and saves.
4. ADMIN truncates the _Shadow tables.
5. PM2 opens PM1's project, makes a change, and saves. Because the
_Shadow tables are empty and PM2 didn't open the project before ADMIN
truncated the _Shadow tables, WinProj would re-save the tasks that were
deleted in step 3 to the database.
6. PM1 is very confused, because he could have sworn he deleted these
tasks...
7. Repeat ad infinitum.

Obviously this I'm just focusing on the task data for simplicity's sake,
but many of the tables in the databases have _Shadow tables... you could
apply the same logic to custom fields, etc. I'll also point out that I
am unfamiliar with all the implications of the cache's reconciliation
process, so it's possible that you could introduce data corruption or
break the system in some other way.

--
Stephen Sanderlin, Project MVP
VP of Technology
MSProjectExperts

For Project Server Consulting: http://www.msprojectexperts.com
For Project Server Training: http://www.projectservertraining.com

Read our blog at: http://www.projectserverhelp.com

 

[Erik K]

Hi Steve,
Thanks for the details! This architecture was obviously designed to support
"normal" project updates... where a relatively small portion of the project
is changed at any one time. I am not actually deleting anything from the
existing enterprise project explicitly, but using an .mpp file and saving-as
the name of an existing enterprise project. It's a wierd scenario, but these
enterprise projects live in a "closed" environment, and there is a project
server installation outside this environment that needs to pass updated
versions of projects on a regular basis. The projects on the inside have
external references that depend on the project uid not changing. Too bad the
PSI cant handle cross-project links I have a lot of customization done to
persist custom field values, verify that enterprise entities are consistent,
etc. and everything seems to work fine. I just know that these shadow tables
will get really massive over time (luckily they are very narrow tables). I
guess I will just need to see if performance suffers in the long run based on
the number of rows in these tables.
Cheers,
Erik

 

[HansH]

Erik,

Have you considered the 'Save for Sharing' feature from Project Server 2007?
You can save an enterprise project 'for sharing'. This extracts an
enterprise project from the project server environment. The 'shared'
project can be carried around on a USB stick an modified on a computer
with Microsoft Project 2007.
Later on, the modified project file can be saved back into the project
server environment.

Some remarks though:
- once a project is saved for sharing, it is checked out and cannot be
modified inside the project server environment
- saving the project back to project server must be done on the exact
same machine as where you saved it for sharing
- you cannot clear the cache on this machine, or the 'shared' project is
useless.

I hope this helps,
Hans

Projectopolis <http://msepm.hsquared.be>

 

[Erik K]

Hi Hans,
Thanks for the idea. Yes, I had considered this, but the projects never
return from the "closed" environment. They are mantained in a Project Server
instance outside of this environment, and updated copies are transferred to
the "closed" Project Server instance when they change.
Cheers,
Erik