Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Deleting Multiple Projects in P6

9 replies [Last post]

Hi,

I have a requirement to datacleanse a P6 installation that has been badly maintained over a 10 year period.

There are circa 4,500 projects (many with up to 50 baselines) that need to be deleted.

The system is running on an old server and is extremely slow, it can take up to 1 hour to delete one project.

Tried to move the projects into a common EPS node so that I could delete the node over the weekend bu the users have used EPS project codes which means that they cannot be moved.

Anyone have a SQL script or know of an application that can handle removal of a large quantity of obsolete projects ???

Replies

Zoltan Palffy
User offline. Last seen 3 days 8 hours ago. Offline
Joined: 13 Jul 2009
Posts: 3089
Groups: None

glad we could help 

Thankyou

Many thanks for your input.

 

Regards

 

 

 

Zoltan Palffy
User offline. Last seen 3 days 8 hours ago. Offline
Joined: 13 Jul 2009
Posts: 3089
Groups: None

David

how did we know that that you knew how to create a new database? we did not we were only trying to help. So you can this info below or leave it. 

When a whole project is deleted all records pertaining to that project are permanently deleted from the database.

If activities are deleted from within the project the records in the database are not removed. They are marked for deletion and removed over time by background jobs.SQL Express does not include a SQL agent to run background jobs on a schedule, so the background jobs run only when logging into P6 Professional

If a large number of entries are deleted from within a project those entries remain in the database to be deleted at login. Due to settings limiting the number of records that can be deleted at one time it may take several logins before all the records marked for deletion are actually deleted. These jobs can take quite a bit of time to complete and cause long load times for P6 Professional.

Deleting activities in P6 Professional and running the background jobs add to the size of the transaction log for SQL and could cause the total database size to reach the SQL Express limit of 10 GB. This causes P6 Professional to generate the error "Bad Public Username or Password. Timeout Expired." when attempting to reconfigure the alias in Database Configuration.

If large numbers of activities are regularly delete within projects it is recommended the database be migrated to the full version of SQL Server. It handles the work done with large projects more efficiently than SQL Express and allows for the background jobs to run at regular intervals and not just at login.

So by default the data_monitor process will not delete records newer than 5 days, so activities deleted within the last 5 days will not be cleared. However you can manually delete them using sql.

to do this logi into sql

Here are the instrucitons

Go to Start Programs Microsoft SQL server 2005, SQL Management Express Studio

the sever name is the name of the computer \Primavera

Authentication is SQL Server Authentication

User name is sa

password is Prima123Vera then click connect

Expand the databases highlight pmdb$primavera

Up top select new query

exec system_monitor    
exec data_monitor 

right click execute 

Re-run the exec data_monitor until it returns 0 for "Total Rows Deleted/Changed:"

now crete a new query 

truncate table refrdel

right click execute 

now close sql 

reboot the system

now you can shirk the database

log back into sql

Go to Start Programs Microsoft SQL server 2005, SQL Management Express Studio

the sever name is the name of the computer \Primavera

Authentication is SQL Server Authentication

User name is sa

password is Prima123Vera then click connect

Expand the databases highlight pmdb$primavera

Right click on the database select Tasks

then Shrik

then database

exit out of sql

this should make deleting projects go much fasters. Remember projects do not clear themselves out until after 5 days 

Emad Mofarej,PMP,...
User offline. Last seen 2 years 48 weeks ago. Offline
Joined: 18 Feb 2012
Posts: 39
Groups: None

I agree with Zoltan and David.

You can delete the projects but it won't resolve your issue completely.

There are five workarounds:

1- Manually: This is by far the safest but as you mentioned it takes time. even with this method which took you 1 hour to delete each schedule you still end up with all global data that was exponentially got bigger and bigger over time. This is your biggest challendge and the main reason that your database is painfully slow.Over the years when the client imported schedule they populated tousands of global calendars resources codes etc and you need to delete those as well. 

2- Primavera API: With little Java programming skill and elbow grease you can code what you need including deleting projects and automate your P6 tasks. I believe this won't save you much time as you can delete multiple projects by selecting them all together and hit delete. Unless you are dealing with deleting tens of baselines in tousands project there is no actual benefit to this method as you may spend more time learining how to code for API. And API uses P6 engine to implement those changes so if it actually takes 1 hour to delete 1 project manually it'll take almost the same time with API or any software that uses the same technology (Legare) to do the same.

3- Accessing SQL server and deleting the data directly: This is the fastest method but you should be carefull. First you should learn how to write a query to delete records. Then you need to learn P6 database Schema and collecting project ID's. Then starting to delete those data. To be honest it is not an easy job to delete all data associated with the projects as the data is not stored in one table and there are lots of tables with diffrent foreign keys which requires lots and lots of knowledge. I wouldn't advise you to do this at all. But if you want to do this you nee to learn these:

Before anything: Backup! Backup! Backup! I can't stress that enough! and follow this at your peril!

a- Delete POBS: Duh! This probably makes your database much smoother as you mentioned there was no database maintenance before.Probably you did it by now!

b-Delete the records in every table that includes the proj_id field based on the PROJECT Table and proj_id that you are looking for. this includes: TASKRSRC, ACTVCODE, ACTVTYPE,TASKPRED, TASKACTV, TASK.  I wouldn't delete the records form PROJWBS or PROJECT table directly and personally prefer to not delete TASK records as well to be in the safer side as I need the P6 to do the rest. If you remove project record from PROJWBS you can't find Project in P6 database as P6 uses PROJWBS to sort Projects in EPS and stores the project name etc.  when you are finished with this step you will end up with a database that is much smoother as all of the affected schedules are now without resource assignments/relationships/tasks codes and Activity (if you deleted the records in activity tab) so you will probably end up with a P6 that is as snappy as it could be after deleting all extra schedules. so if this is not enough you will not get much more improvement from deleting remainings of the projects.

I can't teach you how to  code in java or even VBA. Basically what you need to do is how to import data from sql server to find the proj_id, how to run SQL command in your code to delete related data in other tables. There are plenty of materials out there and the code is not that hard to grasp especially if you know a little bit about programming.  I can give you the manual procedure which you also required to follow in your code.  The SQL login procedure is same as the logic procedure you are using when deleting POBS data (there are plenty articles and procedure available for this) basically run sql command with server adress username and password and select the database. The command for deleting records is this:

DELETE FROM TABLENAME
WHERE proj_id='project id based on the Project table';

GO

for example if I'm looking to delete task resource assignments for project 4628 i would use this code:

DELETE FROM TASKRSRC
WHERE proj_id=4628

GO

you can automate this code by using VBA, Java or any other programming language to delete records related to your chosen projects. You need to delete tables in the order that I gave you. If you try to delete task before resource agsignment the SQL server terminates your command and returns error as there is conflict in your database.Even doing this manually would take less than a minute for each project if you know what you are doing. But you should be carefull not to delete anything by mistake. 

c- All that is left for the unused project is the bunch of useless and small information such as notes, issues, project information and etc. By now your P6 should be much smoother and you can delete the rest manually.

d- You will still end up with ton of global calendar resources and other global data which you can also delete if you know how to categorize them. 

 

 

4- The most convinent option is to backup projects using command line and P6 export function. you can backup all of the usefull projects with one commands and then import it to a new database. Or backup, clear the data base, import again with command line. I don't know how the other programs are accessing the P6 information and by doing this it is highly likely that you will break the link as with importing schedule and resources all of the unique ids will be generated from scratch. you need to find out how the other software read the p6 data. There might be a way if you use import option wisely.

 

5- You mentioned new software installation is out of question. How about upgrading MS SQL server edition. If you are using SQL server express you are only using 1 cpu thread(or core). So no matter how many CPU cores your PC has you still end up using one or tiny fraction of speed. Even when you are using SQL Server advance/web/standard there is always room for improvement. Talk with your IT to see if its possible to upgrade MS SQL server. That might resolve your low speed problem. 

I don't want to sound like a wet blanket but the workaround No 5 might be your only option. As you stated the P6 database is shared for several softwares and this might be the reason for low performance and not the quantity of schedules.

And before everything clean up your P6 data base first. What I mean by deleting is to remove deleted records in primavera and make sure that background jobs are doing their job! Did you run Update Statistics Fullscan? there are lots of improvement before thinking about deleting projects.

I have been using Primavera for 30 years plus. Worked on extremely large deployments including Motorola, GE Alstom, Network Rail, GEC, British Airways, EDF to name a few.

 

Quite aware of how to set up a new database !!!

Legare would not be a chosen application for me as David Kelly run a demo a few years back and it failed. May be worthwhile having another look at it as a possible tool to migrate data when my client upgrades to P6.17.

The issue is that the work I am doing is on a top secret defence contractor site and installing software is horrendously difficult.

Still looking at the options, will do some research at the Project Controls Expo in November.

Biggest issue here is that Primavera is synchronised with Oracle for all reporting and financial management.

The whole business system is ander audit and must maintain compliancy with Sarbanes Oxley.

The company is looking at an upgrade to the latest version of Oracle ERP System over the cloud but this is a long term plan.

Looks like I may have to look for a DBA with SQL skills !!!!

Zoltan Palffy
User offline. Last seen 3 days 8 hours ago. Offline
Joined: 13 Jul 2009
Posts: 3089
Groups: None

I agree with David just leave it there and create a new database

Creating  NEW database

1.   First you need to know the host name of your computer it can be a number or a name. You can find this by going to the control panel and looking under System . Then Computer name Let’s says that’s is it Bob Computer. Write this name down

2.       You need to know the TCP Port Setting.  Go to start in the search box select Regedit 

Look under HKEY_LOCAL_MACHINE and expand this directory

Scroll down to Microsoft SQL Server then to Primavera then to MSSQLServer then to SuperSocketNitLib then to TCP look on the right hand side to see what the TcpPort setting in Primavera is using Write this number down. Let’s say it is 1234 then close rededit.

3.       Find the p6 files folder usually Primavera_P6

4.       Find the install folder

5.       Click on dbsetup.bat

6.       Select install a new database

7.       Select Microsoft SQl Server/Sql Express   

8.       Type in the Product Key EC-01

9.       Select Project Management Database

10.   Select Project Management and Content Repository in one database

11.   In the Sys Admin user name type sa

12.   In the Sys Admin Password type Prima123Vera

13.   In the Database host address type in the name of  the computer that you wrote down in step 1 the example was Bob Computer

14.   In the Database host port type in the number from step 2. The example was 1234

15.   In the Database Name type in any 4 letters that you want to use. Let’s say it is ABCD select next

16.   Check Load License Key and on the right side click on the three … and find the folder where your license is.

17.   Select Install

18.   Open P6

19.   Highlight the new database that you just created (ABCD) then select Configure

20.   The Database alias should be the name of the database that you created in my example it was ABCD

21.   The driver type should be Microsoft SQl select Next

22.   In the Host Name type in the name for your computer\Primavera  in my example it was

 Bob Computer so it should be Bob Computer\Primavera

23.   In the Database name type in the 4 letter database name in my example it was ABCD select next

24.   In the Username type pubuser

25.   In the Password type pubuser

         26.   Select Next then Next again 

David Kelly
User offline. Last seen 1 year 34 weeks ago. Offline
Joined: 19 Oct 2004
Posts: 630

David,

My strongest possible recomendation is not to do it. In my experience you need to make a new database and migrate only those projects you currently need. A P6 database that has not had regular maintenance for 10 years cannot be saved.

And if the client won't spend the money to buy a tool like Collabro's Legare to do the hard work of migration for you, they are not worth saving.