Deleting Multiple Projects in P6

glad we could help 

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 

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

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 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,

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.